Summarising a number of dynamic sheets

[JonesJones]

Hi all,

Over the past few weeks I have been trying to get to grips with how to summarise a number of sheets across Excel. I've been looking at Merge cells from all or some worksheets into one Master sheet but I'm just not that strong at coding so I would appreciate some help.

What I am trying to do is to provide a summary table from approximately 25 sheets in a single workbook. The data that I am trying to summarise can start at any point in the sheet (but will always be superseeded by the merged cell "Tracker". The subsequent 6 columns are what I am trying to summarise into one table but the contents can range from blank to approximately 30 entries (and further).

I need the code to be able to find the correct starting point, copy all of the data until it finds a blank cell and add in the name from a cell at the top of the sheet (i.e. each of the 25 sheets has a different reference).

I don't seem to be able to attach a short example of what I am trying to do but each of the 25 sheets will has 6 columns that need to be copied into the summary sheet. Hopefully this makes sense but if not, please let me know.

If someone could help me to write code that can do this I would be incredibly grateful.

 

[Smitty]

Welcome to the Board!

If you follow the HTML Maker link in my sig you'll be able to post screen shots.

As to the issue, I'm not sure if your layout will support it, but have you considered a Pivot Table with multiple consolidation ranges?

 

[JonesJones]

Hi, thanks.

I'll try and post it later but to answer your other question, I'm not sure how much manual effort a pivot table would be, given that rows are being added across the 25 sheets all of the time.

I'm happy to listen to your suggestion if you think this is a feasible way to go.

 

[Smitty]

Pivot tables are great in that they're dynamic. If you add data, all you need to do is refresh the PT to update it.

 

[JonesJones]

Thanks, I'll take a look.

One complication I see is that, presumably, the Pivot Table has to exist within the workbook ? The reason I ask is that I have 4 of these workbooks to consolidate so I'm not sure how I would get one Pivot Table dynamically updating from these workbooks.

What do you think ?

 

[Smitty]

If you use PowerPivot you can consolidate data from almost anywhere. Think of it as PivotTables on steroids.