Macro to update and populate a summary table with data from multiple and new sheets added.

Hi there!

My conundrum:

There are multiple sheets in a workbook that are all duplicates of one another in terms of layout and design but with varying data between them.

Because these data sheets are the same layout, the specific cell that is required to be represented in the summary sheet's table is always in the same location across all tabs.

I want the macro to be flexible in that it can cope if further data sheets are added, updating the summary table (especially the TOTAL row) and inserting new rows for each of the new added data sheets.

If anyone could offer any guidance it would be greatly appreciated.

Attached is an excel workbook that attempts to resemble my problem and if anyone can provide the macro needed to get this working then I can attempt to transfer that over to my actual spreadsheet I am working on.

Thanks!

Another option

I was tempting to use dictionary scripting, but I suspect you have a MAC.

Hi XLAdept,

that is one neat solution from which I have discovered a couple of new techniques that would never have occurred to me (enthusiastic amateur with no training in VBA or Excel other than some experience). My solution would have taken many more lines of code for sure (I resolved a very similiar issue for another thread in the last couple of days)

However, it seems to me while stepping through the code that the " Range("A" & F.Row) = wu.Name" statement should probably be
"Range("A" & F.Row - 1) = wu.Name"

In my testing it looks like you were adding in the new sheet on top of the total line and not in the blank line you had just created

Happy to hear why I am wrong as I am still learning

Jmac1947

sorry, forgot to subscribe to thread

Hi xladept,

based on some solid testing (like one executoin to see the error and another to replicate it, but not a third to test my assumption) you are overwriting the total line.

I just realised my suggestion will not work either so i am a bit stumped as well

I tried a bit more playing around (remember I am not in my comfort zone here) and ended up with this:

Please Login or Register  to view this content.

This is happily inserting the line and updating values correctly but seems to be having an issue with the sheet names in col A

Hi xladept,

Yep, just updated the macro on my machine with your latest code from post #2 and it works a treat in my testing

Hi xladept,
Will send you a solution at some time today(Too busy right now)