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.

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

Continue reading Macro Creates Excel Pivot Table From Multiple Files