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

**negomi** · 01-12-2014, 04:02 PM

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!

**xladept** · 01-12-2014, 06:26 PM

Hi Imogen,

Here's a start:

Sub Imogen(): Dim ws As Worksheet, wu As Worksheet, S As Range, F As Range
Dim A As Single, B As Single, C As Single, D As Single, r As Long
Set ws = ActiveWorkbook.Sheets("Summary"): Set S = ws.Range("A:A")
For Each wu In ActiveWorkbook.Worksheets
If wu.Name = ws.Name Then GoTo GetNext
Set F = S.Find(wu.Name)
If Not F Is Nothing Then
r = F.row: GoSub LoadTable
Else
Set F = S.Find("TOTAL"): r = F.row
F.EntireRow.Insert
Range("A" & r) = wu.Name
GoSub LoadTable
End If
GetNext: Next
                            Exit Sub
LoadTable:
ws.Range("B" & r) = wu.Range("B1"): ws.Range("C" & r) = wu.Range("D5")
ws.Range("D" & r) = wu.Range("C10"): ws.Range("E" & r) = wu.Range("F14")
Return
End Sub

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

And, you should be ready to go

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

**jmac1947** · 01-12-2014, 06:55 PM

sorry, forgot to subscribe to thread

**xladept** · 01-12-2014, 10:04 PM

Hi jmac,

I think I fixed it - did you try the corrected post #2??

**jmac1947** · 01-13-2014, 12:38 AM

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

**AB33** · 01-12-2014, 06:30 PM

Another option

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

**jmac1947** · 01-12-2014, 06:54 PM

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

**xladept** · 01-12-2014, 07:01 PM

Hi jmac,

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"

I didn't test this but I expected that the F.Row would be the new blank one - didn't that work out?

You're right I've edited that post - Thanks

**jmac1947** · 01-12-2014, 07:17 PM

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

**jmac1947** · 01-12-2014, 07:29 PM

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

    Set F = S.Find("TOTAL"): F.EntireRow.Insert
    Range("A" & r) = wu.Name
    r = (F.Row - 1): GoSub LoadTable

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

**xladept** · 01-12-2014, 10:10 PM

@AB33 - I'd love to see how you would use the dictionary scripting with this (I'm new to dictionary and enthused about it ever since a post by snb)

**AB33** · 01-13-2014, 05:15 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Re: Macro to update and populate a summary table with data from multiple and new sheets ad

Thread Information

Users Browsing this Thread

Similar Threads

How to create a summary sheet that will auto-populate as new tabs are added?

[SOLVED] Writing a macro to collect data (selective) from multiple sheets to a summary sheet

Is there a way to update the pivot table as data is added to the original worksheet?

how to update pivot table when new rows/columns are added to the original data?

[SOLVED] data entered on multiple sheets also added to summary sheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions