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

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

Post by A & » Fri, 31 Jul 2009 04:43:42


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

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

nputting 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 another sheet that references the new sheet

Post by jasontferr » Fri, 31 Jul 2009 04:59:20

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