On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot table macro, and here it is — in today’s example, Kirill combines data from a sales list and price list, stored in separate workbooks. The macro combines the data and calculates the selling price for each item, then creates a pivot table from the results.
Thanks, Kirill, for providing these two pivot table macros, and the powerful techniques they demonstrate for creating pivot tables from data in different locations.
The Report Workbook
Like the previous example, in this sample there is a main file named Report.xls, that 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.
In the example, the orders are in the DB-Sales.xls file, and the price list is in the DB-Items.xls file. In each file there is an Item_ID column, and those will be connected in the macro.
After you click the button, the macro creates a pivot table from the two data files, which are stored in the same folder. You can refresh the pivot table, to show the latest data in the source files, as long as all the files stay in their original location.
The Inner Join Query
Kirill’s macro creates an inner join query to combine the data from the selected files and calculate the sales price. In the Connection Properties dialog box, you can see the SQL string for the inner join query, with the Units * Unit Price calculation. The two files are connected by the Item_ID field, so the item name and unit price for each order can be displayed.
Download the Sample File
To see Kirill’s pivot table code, you can download the Pivot Table Join Calculation example. The zipped folder that contains the Report.xls file, and the two 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.