inserting a formula with reference to a new sheet in a cell using VBA

[Peer]

I've quite a problem I think.
I've made a userform in which the name of a new sheet can be defined
as follows:

Dim ws As Worksheet
Set ws = Worksheets.Add
ActiveSheet.Name = postnaam.Text (postnaam is the name I've given t
the textbox in which the name of the sheet is defined by user)

Later on I want to fill in a formula in another (total calculation
sheet with a reference to the new sheet, for example:

worksheets("total sheet").range("D8").formula = "='name of th
sheet'!$D$8

The problem I now encounter is that I don't know which name the use
will use for his new sheet, so I can't make an absolute reference, ye
this is what excel needs for automatically updating the value in tha
cell.

Please help me with this problem, I can't find a solution!

tnx in advance,
Pee

 

[Bernie Deitrick]

Peer,

The solution is already in your code:

Set ws = Worksheets.Add

But you should use it this way:

Set ws = Worksheets.Add
ws.Name = postnaam.Text....

If all this code is run at the same time, you still have the name available:
worksheets("total sheet").range("D8").formula = "='" & ws.name & "'!$D$8"

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

You can save the name of the sheet in a defined name

Thisworkbook.Names.Add Name:="NewSheet1", Refersto:= "=""" & postnamm.Text &
""""


Then you can use this to get the name

Dim sShName as String
sShName = Evaluate(thisWorkbook.Names(NewSheet1"))
worksheets("total sheet").Range("D8").formula = "='" & sShName & _
"'!$D$8"