Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with copying of excel spread sheet from template trought VB | Excel Programming | |||
Copying a formatted "template" sheet and naming new sheet via user form | Excel Programming | |||
Copying sheet containing GetPivotData, new sheet references old workbook! | Excel Programming | |||
copying sheet references that refer to a cell in the preceding she | Excel Worksheet Functions | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |