copying a template sheet and inputting a formula into another sheetthat references the new sheet
 

Hi here is the section of code that I am having problems with, I have
it commented to help anyone understand what I am doing. Everything
works except for the last line of code. The big picture of what I am
trying to do is to make a copy of an existing template worksheet that
I have setup, then I want the macro to input a formula into the my
Main worksheet that references cells in the newly created worksheet.


'This variable store the name of the new sheet that it gets from an
existing cell
newSht = ActiveCell.FormulaR1C1

‘Creating a copy of the template worksheet and renaming it to the
value stored in variable newSht
Sheets("Worksheet Template").Select
Sheets("Worksheet Template").Copy Befo=Sheets(3)
ActiveSheet.Name = newSht

‘Inputting a formula to the main worksheet that references the newly
created template copy
Sheets("Main Worksheet").Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=('newsht'!R30C7*'newsht'!R51C7)"



Thanks

copying a template sheet and inputting a formula into anothersheet that references the new sheet
 

Change the last line as follows:
ActiveCell.FormulaR1C1 = "=('" & newsht & "'!R30C7*'" & newsht & "'!
R51C7)"
The variable needs to be referenced outside of the quotation marks.