Excel dependent drop down list from pivot table

Comments [20]

20 Responses to Change PivotTable data source using a drop-down list

1. Jeff says:

   May 8, 2012 at 4:02 pm

   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.

   Reply
2. Oscar says:

   May 14, 2012 at 12:43 pm

   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

   Reply
3. Jennifer Long says:

   July 6, 2012 at 11:02 pm

   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.

   Reply
4. Oscar says:

   July 14, 2012 at 7:13 pm

   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.

   Reply
5. Anselmus Basunanda says:

   October 11, 2012 at 8:03 am

   Dear Oscar,
   Is it possible to make change not pivot table, but pivot chart using drop down list of several pivot table ?

   Reply
6. Bill says:

   October 29, 2012 at 1:29 pm

   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.

   Reply
7. Oscar says:

   October 31, 2012 at 10:30 am

   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 Sub

   Reply
   • Bill says:

     November 1, 2012 at 5:22 pm

     Many Thanks Oscar,
     Works fine.

8. Andrew says:

   April 24, 2014 at 3:04 pm

   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.

   Reply
   • Oscar says:

     April 25, 2014 at 9:32 am

     Andrew,

     I found this:
     //blog.laptopmag.com/excel-2013-pivot-table-from-multiples

9. Alex says:

   August 11, 2014 at 4:32 pm

   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!

   Reply
10. narendra says:

    January 27, 2015 at 11:42 am

    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.

    Reply
11. Louis says:

    February 24, 2015 at 3:42 pm

    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.

    Reply
12. Katie says:

    March 13, 2015 at 8:50 pm

    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?

    Reply
    • Oscar says:

      March 16, 2015 at 1:13 pm

      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

13. Beauxnurr Hertz says:

    July 22, 2016 at 2:09 am

    Thanks for writing this!! It's exactly what I was looking for.
    BNR

    Reply
14. prashant says:

    November 18, 2016 at 5:33 pm

    hello Oscar,

    please guide how to change 2 pivot tables at a time.
    the code you shared can change 1 pivot at a time

    Reply
    • Oscar says:

      November 19, 2016 at 10:15 am

      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 Sub
15. Anoop Jain says:

    March 24, 2020 at 5:45 pm

    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

    Reply
    • Oscar says:

      April 3, 2020 at 1:05 pm

      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?

Video liên quan