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?

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]"

--
Regards,
Tom Ogilvy


"Eric Silva" wrote in message
...
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?

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?

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.

"Eric Silva" wrote:

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?

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


"Eric Silva" wrote in message
...
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.

"Eric Silva" wrote:

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?