Change PivotTable data source using a drop-down list
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
- Press with left mouse button on "Insert" button
- Press with left mouse button on combo box (form control)
- Create a combo box
Want to learn more about Combo Boxes? Read this article:
Recommended articles
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Select input range
- Press with right mouse button on on combo box
- Press with left mouse button on "Format control..."
- Go to "Control" tab
- Select an input range (D2:D4)
- Press with left mouse button on 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.
- Press with right mouse button on on your workbook in the project explorer window.
- Press with left mouse button on "Insert".
- Press with left mouse button on "Module".
- Copy the code shown below and paste to code module, see image above.
- Return to Excel.
Sub ChangeDataSource()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
End With
End Sub
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.
Assign a macro
- Press with right mouse button on combo box
- Select "Assign macro..."
- Select ChangeDataSource macro
- Press with left mouse button on OK!
Combobox category
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Pivot table category
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
Excel categories
20 Responses to “Change PivotTable data source using a drop-down list”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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.
Hello Oscar,
I used your code above and it worked wonderfully. Despite being my first macro I have ever used I even managed to alter the code to get it to control 3 pivot tables at the same time based on one combo box.
I now have a more difficult problem. I need to consolidate some of my tables into my pivots.... I have 43 different tables, and some times I need to "join" / "consolidate" / "sum up" different tables. So for example if I wanted to create an "entire company consolidated" pivot I would need to join all 43 tables together.
Andrew,
I found this:
https://blog.laptopmag.com/excel-2013-pivot-table-from-multiples
Hi Oscar,
I found this very helpful! So thank you very much for this! However, I have one issue with it. I have multiple pivot tables that I'm trying to switch between and each one of them have different values (Revenue, GOP, NI, etc.).
So, under your code, each time I switch between them, I have to add the values that I'm looking for. Is there a way to make is so that I don't have to do this each time?
Any help would be appreciated. Thanks!
this code is good but this is not working for me.i am getting unable to get the pivot table property of the worksheet class.what it means please help me i am new vba.
Hi Oscar,
Your code is very helpful, thank you. But I have a little problem. I have 5 tables with I did one pivot table, and other 4 pivot tables with 5 tables each one, the data is almost the same. I want to change the 5 pivot tables with one drop down list. Is it possible?
Thanks for your help.
Neat trick! Thanks for sharing! I'm pretty new to VBA. How would you get this to work with named data ranges in separate Excel workbooks?
Katie,
I am not sure this works, change the table names in the drop down list to:
[Book2]Sheet1!namedrange
Book2 - Your workbook name
Sheet1! - Your worksheet name
namedrange - Your named range
Thanks for writing this!! It's exactly what I was looking for.
BNR
hello Oscar,
please guide how to change 2 pivot tables at a time.
the code you shared can change 1 pivot at a time
prashant
Sub ChangeDataSource() With ActiveSheet.Shapes(Application.Caller).ControlFormat Worksheets("Sheet1").PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ .List(.Value) Worksheets("Sheet2").PivotTables("PivotTable2").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ .List(.Value) End With End SubHello,
I am trying to update 3 pivot tables with the selection from Drop Down menu. I used your code but it is giving me a run-time error 1004 (syntax error)
on code -
Worksheets("Sheet2").PivotTables("PivotTable2").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
Could anyone explain what it does and how to fix the issue with ---> SourceData:=_ ?
I really appreciate your assistance.
Regards,
Anoop
Anoop Jain
The code uses the selected value from the drop down list to change the Pivot Table's data source.
Perhaps you are using a value that is not an Excel Table?