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 […]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
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. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
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 […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to count unique […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
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!
Many 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
Hello,
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?