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?