Adding new sheet's cell value to old formula

[Guest]

I have a summary sheet and I want to add data sheets so that they reference
into the summary sheet automatically. I made a command button that accesses
a macro to create, name, and move the sheet where it needs to go. Now all I
have to do is add the values from the new locations to the summary sheet's
formula.

Here's shat I tried:

Range("C13").Select
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + " +
'&sheetname'!R[5]C[3]"

where sheetname is the string variable associated with the name of the new
sheet. Problem is that excel won't accept a variable value for a sheet name.
I know there's got to be a better way to do this. Any hints?

 

[Tom Ogilvy]

Assume the new sheet name is Sheet10:
Dim sheetname as String
sheetname = "Sheet10"
Range("C13").Select
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
" + '" & sheetname & '!R[5]C[3]"

 

[Guest]

At first, I copy-pasted the code into my program, then I compiled, got an
error, read through it and saw only three quotes. I added a quote to the
line right before the second apostrophe and got this:

ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " + '" & sheetname &
"'!R[5]C[3]"

This gives me the 1004 error, so it's still not reading something in the
second half of the statement as the correct object type to be going into an
excel formula. What's still wrong?

 

[Guest]

I don't know what I did but it suddenly started working just right. Thanks a
lot for the help, Tom. I read some of your other posts before I put this one
up and you're both knowledgeable and helpful. Keep it up! I might have
another question someday.

 

[Tom Ogilvy]

Just for completeness. there was a typo

ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
" + '" & sheetname & '!R[5]C[3]"

should have been

ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
" + '" & sheetname & "'!R[5]C[3]"

--
Regards,
Tom Ogilvy

I don't know what I did but it suddenly started working just right. Thanks a
lot for the help, Tom. I read some of your other posts before I put this one
up and you're both knowledgeable and helpful. Keep it up! I might have
another question someday.

At first, I copy-pasted the code into my program, then I compiled, got an
error, read through it and saw only three quotes. I added a quote to the
line right before the second apostrophe and got this:

ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " + '" & sheetname &
"'!R[5]C[3]"

This gives me the 1004 error, so it's still not reading something in the
second half of the statement as the correct object type to be going into an
excel formula. What's still wrong?