Adding multiple worksheets to a file and having a summary page automatically update

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
I am creating a file where users will use an input worksheet to enter in some key data each day. Each day will have it's own worksheet but not necessarily one for each consecutive day (days can be skipped). As worksheets are copied and renamed to create a new day, I would like a summary worksheet to recognize its creation and pick up some key data.

I've included a dummy workbook with basic direct link formulas to show what is being picked up. (Highlighted in yellow in the workbook). Linking each worksheet manually as it is added is not the solution I'm looking for.

I've contemplating pre-populating the workbook with multiple worksheets but that is not my ideal solution.

Finally, I would like the calculation strain on the document to be as minimal as possible as extended waiting time to recalculate as data is entered is not desirable.

Does such a solution exist? I'm open to moving layout of cells around if that happens to make the solution easier
 

Attachments

  • Sample Data for Excel Forum.xlsx
    14.9 KB · Views: 18
Did you ever find a solution to this?

I'm too new to VBA to do it for you but this seems like something to put in the VBA chat and I'm sure someone would be able to sling you some code. I'm thinking it would just need to pull particular data points from the worksheets if your data is always formatted consistently, and then just loop through all worksheets in the workbook. Code could execute upon opening the workbook and you'd be good to go.
 
No I haven't found a good solution yet. In my case once the templated worksheet is duplicated, it may change in that rows could be added if a particular day has a heavy amount of one category. so for instance if the total for that category was set up on cell F15, it may well end up on cell F23 (or any other similar range past row 15) in some cases. The solution would have to start with code in the various worksheets finding the various data points and exporting them to the summary sheet. At which point it would have to create a new row in the summary to house the data in the newly duplicated worksheet.
 
Back
Top