+ Reply to Thread
Results 1 to 2 of 2

Consolidating multiple external pivot tables into one pivot table

  1. #1

    Consolidating multiple external pivot tables into one pivot table


    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

  2. #2
    Forum Contributor
    Join Date
    You ask for suggestions perhaps using MSquery but say you have already tried 'external sources'. I dont know how to use one without the other so perhaps the following suggestion may not be useful to you.

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

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

    I hope it helps.

    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1