Change pivot table data source using a drop down list
In this excel 2007 tutorial 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
Select input range
- Right click on combo box
- Click "Format control..."
- Go to "Control" tab
- Select an input range (D2:D4)
- Click OK
Cell range D2:D4 contains the table names, you probably need to change these names.
Add vba code to your workbook
- Press Alt+F11
- Select your workbook in the project explorer window
- Insert a new module to your workbook
- Add code below to code window
- Return to excel
Sub ChangeDataSource()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
End With
End SubFor 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.
Assign a macro
- Right click combo box
- Select "Assign macro..."
- Select ChangeDataSource macro
- Click OK!
Download workbook
Pivot table - change data source range vba.xlsm
Related posts:
Change chart data range using a drop down list (vba)
Why use Pivot table and pivot chart?
How to create a dynamic pivot table and refresh automatically in excel
Excel vba: Populate a combobox with values from a pivot table
Recommended categories
My pivot table is called "PivotTable"
I created a macro, put the code in:Sub ChangeDataSource()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
ActiveSheet.PivotTables("PivotTable").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
End With
End Sub
Now when I try to change the data source via drop-down i receive this error: Run-time error '1004' Reference is not valid.
Jeff,
I am guessing, the names in the combo box don´t match the table names in your workbook?
How to find the table names
1. Select a cell in a table
2. Go to tab "Design" on the ribbon
I am also getting a run-time rror'1004: Application-defined or object-defined error on:
ActiveSheet.PivotTables("PivotTable2").PivotTableWizard SourceType:=x1Database, SourceData:= _
.List(.Value)
I changed the pivot table name as instructed above, but that has not corrected the error. Any suggestions? Thanks.
Jennifer Long,
Is the attached file working?
Did you spell the table names correctly? (Read my answer to Jeff)
I get the same error if I use a table name that does not exist.
Dear Oscar,
Is it possible to make change not pivot table, but pivot chart using drop down list of several pivot table ?
Hi Oscar,
I found your code very helpful and easy to apply despite that I am a VBA bigginer.
In my case the pivot table is in a different worksheet named "data2" , from the Combo box which is in "Data1".
Is there any way to modify you code so I can still update the pivot table?
Many Thanks.
Bill,
Yes there is!
Sub ChangeDataSource() With ActiveSheet.Shapes(Application.Caller).ControlFormat Worksheets("data2").PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ .List(.Value) End With End SubMany Thanks Oscar,
Works fine.