In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down list. The tutorial workbook contains three different tables (Table1, Table2 and Table3) with identical column headers.
In this tutorial:
Create a combo box (form control)
Select input range
Add vba code to your workbook
Assign a macro to drop down list
Create a Combo Box (Form Control)
Go to the developer tab
Click "Insert" button
Click combo box (form control)
Create a combo box
Want to learn more about Combo Boxes? Read this article:
Cell range D2:D4 contains the table names, you probably need to change these names.
Add VBA code to your workbook
Right-click on your workbook in the project explorer window.
Copy the code shown below and paste to code module, see image above.
Return to Excel.
ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
For this code to work you need to change pivot table name (bolded) in vba code: PivotTables("PivotTable1") to the name of your specific pivot table.
Note, remember to save our workbook with file extension *.xlsm (macro-enabled workbook) in order to keep the VBA code attached to the workbook.