Consolidate Multiple Data Tables

**dangermouse1981** · 06-04-2011, 12:03 PM

Hi there,

I'm looking to consolidate multipe data tables in to a master table, so that the master table updates when the source tables are changed.

The columns are fixed and identical, but the number of rows in the source tables is undetermined (refreshes from various other XML sources).

The ulitmate goal is to base a pivot on the Master table - I've tried the consolidate multiple ranges feature for that, but I need more access within the pivot.

Any suggestions appreciated.

Cheers,

Steve

**MarvinP** · 06-04-2011, 01:03 PM

Hey Dangermouse1881,

Be careful what you ask for. Find the attached with lots to learn.. I wrote some VBA code that clears the Master sheet and then grabs all the sheets to the right of the first and appends the data on the Master Sheet.

Then the attached has a Dynamic Named Range of MasterDataTable that expands or contracts based on how big the master table is.

Then the Pivot Table is shown which uses the Dynamic Named Range as it's source data.

I've created some fake data of Col A = dates and Col B = Amounts so you could test it out. Add more sheets if you like or add more data to any Source Sheet.

Finally I created a button on the Master to run the VBA code.

hope this helps.
BTW - if this data and the code don't work you should supply a sample of your data so the code can be modified to work with your data. Caution - On Row 1 of all sheets you can't have stuff to the right of the data table. This will make my ColCnt wrong and might mess things up.

I'm attaching the .xlsm and the .xls because I'm not sure which version you have. The .xls may have some compatibility problems with the RandBetween formula and some grouping on the pivot.

**dangermouse1981** · 06-04-2011, 01:23 PM

Wow thanks Marvin, this looks very comprehensive.

A couple of quick questions:

- Is it possible to modify this solution to use the Excel 2007 table feature (sorry I should have mentioned version at the start) rather than dynamic ranges?

- If the above is possible, could I supply the VBA code with a range that includes references to all names of the tables I want to include in the append?

You're right about the learning! The VBA is a little beyond me here, I can see the basic loop structure going on but thats about it so far!

Thanks again,

DM

**MarvinP** · 06-04-2011, 02:25 PM

Hi DM,

Anything (mostly) is possible but sometimes there are easier ways to do them. If you supply a sample I can look. You will need to explain why you want to use Tables on the Source sheets. I'm a little concerned/confused about a table that is filtered and if all records will be copied from the source to the master. Sometimes great ideas conflict with each other.

Keep learning VBA and ask questions about what statements do. Learn how to set breakpoints in VBA and step through the code as this is a great learning tool. Read http://www.cpearson.com/excel/Debug.htm

**Wissam1** · 07-15-2011, 11:57 AM

this sounds similar to an issue I'm having, with a twist: I need to be able to append (combine) or consolidate a few tables into one master table, but WITHOUT using any code or other applications...Can this be done??? Thanks...Wissam