Sum Across Sheets

[j_Southern]

I use a worksheet as a template which is used to generate 12 sheets for each month, by copying the template to newly created sheets.
The sheets are named as months

The template contains formulae which usually copy across properly.
However some of the formulae are used to generate yearly summaries by using formulae such as
SUM(JANUARY:DECEMBER!Z1). In these cases the sheet names are rendered as #REF!

The code for copying the template is :-

Please Login or Register  to view this content.

I have tried workarounds without success so I would be very grateful for some help to copy the formulae across properly.

[j_Southern]

I have seen a fault in the code I quoted, it should be-

Please Login or Register  to view this content.

I am sorry about this and I hope that it has not wasted someones time. The problem still remains

[j_Southern]

I have realised that my problem was caused because the old sheets were deleted before the code was executed. This meant that the template sheet was referring to non existent sheets hence the #REF!.
I therefore turned to the following piece of code run after the new sheets were created.

Please Login or Register  to view this content.

This simply puts a string in AQ6 not the formula which would look like MARCH!AQ6 + APRIL!AQ6 + MAY!AQ6 etc, accumulating as the months progress.
I think I have misunderstood FormulaR1C1
can anyone help
John

[FDibbins]

Perhaps a way to overcome this would be to try the following trick...
if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though