Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically change accumulation links when adding worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically change accumulation links when adding worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically adding code | Excel Programming | |||
Quick question about dynamically adding and naming worksheets | Excel Programming | |||
Adding Pivot Tables Dynamically | Excel Programming | |||
Emergency ( Adding Worksheets dynamically in Workbookds) | Excel Programming | |||
Adding button dynamically | Excel Programming |