How to Consolidate Pivot Tables Using VBA

The VBA language for Microsoft Excel lets you create modules that run in your Office applications. You can consolidate your pivot tables in Excel using the interface to select all of the pivot table information and recreate the table. The new range becomes the pivot table for your spreadsheet. You can consolidate tables on the same spreadsheet or on separate spreadsheets.

Instructions

1. Open the Excel file you want to edit. Click the "Developer" tab and click the "VBA" button. If the Developer tab does not display, click the "File" tab and click "Options." Click "Customize Ribbon" and select "Developer" in the list of tab options. Click "OK" to save the changes. The Developer tab displays, so you can open the VBA editor.

2. Set the range of data you want to use to consolidate the pivot tables. Add the following line of code to set the range for the pivot table creation:

   Range(Selection, Selection.End(xlDown)).Name = "Data"

3. Create the pivot table's data. The following code adds data from a database to the file:

   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
   SourceData:="=Customers").CreatePivotTable TableDestination:="", _
   TableName:="Customers"

   In this example, a database table named "Customers" is used for the pivot table data.

4. Add the new pivot table to the spreadsheet. The following code adds the table to the third column in the spreadsheet:

   ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 0)

References

• OzGrid: Excel Tips and Tricks
• Microsoft Developer Network: How to Show the Developer Tab on the Ribbon