Macro to add new sheet and then fill formula in a totals sheet to refernce this sheet
 

Hi all,

I have created a macro to copy the template sheet and name the sheet based on the last sheet, so sheet names are 1,2,3 etc

Sheets("TemplateSheet").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Previous.Select
Range("C6").Copy
ActiveSheet.Next.Select
Range("c6").PasteSpecial xlPasteValues
Range("c6").Value = Range("c6").Value + 1
ActiveSheet.Name = Format(Range("c6").Value, "#")

This works fine but i also want to add to the macro to copy this formula =IF('1'!A$3="","",'1'!A$3) to the next row but to reference the new worksheet so it would be =IF('2'!A$3="","",'2'!A$3). Does anyone know how I can do this?

Thanks

Macro to add new sheet and then fill formula in a totals sheet to refernce this sheet
 

Hi Sophs,

Am Thu, 12 Jul 2012 15:17:38 +0000 schrieb Sophs:

Sheets("TemplateSheet").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Previous.Select
Range("C6").Copy
ActiveSheet.Next.Select
Range("c6").PasteSpecial xlPasteValues
Range("c6").Value = Range("c6").Value + 1
ActiveSheet.Name = Format(Range("c6").Value, "#")

try instead:
Sheets("Template Sheet").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = _
Sheets(Sheets.Count - 1).Range("C6") + 1

This works fine but i also want to add to the macro to copy this formula
=IF('1'!A$3="","",'1'!A$3) to the next row but to reference the new
worksheet so it would be =IF('2'!A$3="","",'2'!A$3). Does anyone know
how I can do this?

Which cell contains this formula?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2