Consolidating multiple external pivot tables into one pivot table

[Exiled]

Hi,

Here's the situation: I have 30-40 workbooks that are updated on a
weekly basis (the data for sure, the filenames might also change for
some of them), each includes a sheet with a pivot table (same fields
and layout in each, but pulling from their own unique ranges within
their respective workbooks).

I would like to somehow pull the data from each of these pivot tables
into one master file, and preferably then I can create one master pivot
table as well to organize the consolidated data.

I've tried my hands at using external sources and consolidated ranges,
but my expertise is limited in this area. Are there any VB macros, MS
queries, or anything else that would be the answer to my problem?
Thanks in advance for your help!

FYI: Using Excel 2002

 

[steven1001]

You ask for suggestions perhaps using MSquery but say you have alread
tried 'external sources'. I dont know how to use one without the othe
so perhaps the following suggestion may not be useful to you.

The attached zipfile contains a spreadsheet (in Excel 97!) that create
a consolidation from three other spreadsheets in the zipfile that al
contain data that could be similar to what you describe. You will not
that this approach consolidates the data from the other spreadsheet
rather than the pivot tables in those spreadsheets. As you say th
data is in the same format in each spreadsheet this should not be a
issue (well it simplifies the sql).

As you have so many spreadsheets to consolidate you might want to use
spreadsheet to actually create the sql for MSquery so that column
contains the spreadsheet name and column B constructs the sql b
insering the value in column A in all the appropriate places.

I hope it helps.

regards.

+-------------------------------------------------------------------
|Filename: Consolidated spreadsheets.zip
|Download: http://www.excelforum.com/attachment.php?postid=5183
+-------------------------------------------------------------------