Dynamically change accumulation links when adding worksheets

[Guest]

I have a workbook in which a Summary worksheet accumulates data from a Detail
worksheet. Quite often I need to have extra Detail worksheets which I create
using [hold Ctrl key down, Edit, Move or Copy worksheet, Copy worksheet] as
many times as I need detail worksheets. This renames each copy and updates
all links in the Detail worksheet.

But now I have to manually update the Summary worksheet. ie.
Summary.Total = Detail 1.Subtotal + Detail 2.Subtotal + Detail 3.Subtotal
etc. etc.

Is there a way to dynamically update the cell formulae in the Summary
worksheet to link to the Subtotal cells in the Detail worksheets, as and when
I make new Detail worksheet copies.

I have tried to find a way in Excel functions and also using VBA macros, but
can not find a way to achieve it.

Anybody have any suggestions??
Thanks in advance.

 

[Guest]

U can use the worksheet_activate event of the Summary worksheet. Using VBA
code you can use the sheet number to determine the most recent Detail sheet,
or some other unique pattern. Then you can update the formula in the Summary
sheet with the correct reference. Jason