If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality.
Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional pivot table from data on different worksheets.
More Pivot Table Macros
Now, Kirill is back to share two more pivot table macros, and I’m sure you’ll be impressed by both of them. In today’s example, he’s written a macro to create a pivot table from multiple Excel files.
This is based on a solution that Kirill posted in one of Russian-speaking Excel forums for generating a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook).
The Report Workbook
The main file is named Report.xls, and it contains Kirill’s pivot table code, and a button that runs the macro. When you open the file, enable the macros, then click the Create Pivot button.
The Data Files
When you run Kirill’s macro, it prompts you to select one or more data files, all stored in the same folder. In this example, the files for Alberta, Ontrio and Yukon are selected.
All of the data files must have the same structure, and the macro works with the data stored on Sheet1 in each file. In the screen shot below you can see Sheet1 in the Alberta and Ontario files, which have identical column headings.
The Pivot Table
After you select the files and click Open, the macro creates a pivot table based on the data from Sheet1 in each of the selected files. Because the pivot table is based on a Union query, and not built from Multiple Consolidation Ranges, it’s a fully functioning pivot table, and you can pivot, group and filter the data, as usual.
You can also refresh the pivot table, to show the latest data in the source files, as long as those files stay in their original location.
The Union Query
Kirill’s macro creates a Union query to combine the data from all the selected files. If you open Microsoft Query, you can see the SQL string for the Union query, and all the data from the selected workbooks.
Download the Sample File
To see Kirill’s pivot table code, you can download the Pivot Workbooks example. The zipped folder that contains the Report.xls file, and the five sample data files.
Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.
Pivot Table Macro #2
Come back on Wednesday to see Kirill’s second pivot table macro. It’s another creative twist on creating a pivot table from data in different workbooks.
______________
How about multiple files with multiple sheets? Can this be done?
Just to say thank you!!!!
I have the same question as Matthew. I have a workbook template that contains approximately 15 worksheets gathering different types of information and is distributed to approximately 100 different locations. This template is then summarized in another workbook with manual links to each and every other workbook…very cumbersome. This solution appears to be perfect. I would want to create a different pivot table for each worksheet – not one combined pivot table. Your thoughts?
Dear Debra,
I have a problem with the calculated field. Everytime when I try to do a calculated field to the pivot table, the values come out either 0 or just calculates the first column only. I am doing simple calculations only. (=A+B-C+D-E)
I cannot get this to work with XL2007. The only changes I made were to save the example data files as xlsx, save the report file as xlsm and change .xls in the code to .xlsx in the two places .xls occurs (arrFiles = …). Can anyone help? I would love to be able to use this!
Hi
I can get this to take information from one workbook but when I select 2 or more it says ‘Cannot group that selection’
Section that it asks me to debug is: .DataRange.Cells(1).Group _
Start:=True, _
End:=True, _
Periods:=Array(False, False, False, False, True, False, True)