Hello,
Sorry if the title may not be the best description of what I am trying to do.
I have a macro that copies 3 sheets in my workbook (Invoice1, PC1, CO1).
It copies the data on the different sheets and creates new sheets for them, updating the sheet name to Invoice2, PC2, CO2, and so on (3,4,5..).
I need formulas copied from the new PC to the new Invoice but I cannot find out how to make this work.
Here is some code for my sheet creation:
'Create Invoice Sheet
Sheets(2).Select
Cells.Select
Selection.copy
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Paste
'Invoice Sheet name
Dim i As Long, InvoiceX As String, temp1 As String
InvoiceX = "Invoice2"
If WorksheetExists(InvoiceX) Then
temp1 = Left(InvoiceX, 7)
i = 1
InvoiceX = temp1 & i
Do While WorksheetExists(InvoiceX)
i = i + 1
InvoiceX = temp1 & i
Loop
End If
ActiveSheet.Name = InvoiceX
So the invoice sheet which changes is titled InvoiceX. The other formulas the same with different variables (j and k instead of i, etc).
Here is what I tried to do to copy formulas:
Range("H2").Select
ActiveCell.FormulaR1C1 = "[InvoiceX]!RC[1]"
Can someone please shed some light on what I am doing wrong here?
Please let me know if additional information is needed or if I was unclear in any explanations.
Thanks in advance.
Bookmarks