none
VBA Pivot Tables RRS feed

  • Question

  • Hello,

    Wondering if someone can help.

    I have a spreadsheet with 3 pivot tables on it.  They have 2 rows ("Country" & "DispenserType"). This is them Summed by Nz2. (Technical term don't worry).

    What I want to do is whenever I filter the first pivot table (Pivot Table1) it will then use this and filter pivot table 2 and 3.Ie. show all Spain. so I filter by Spain and pivot tables 2/3 do the same.

    This is the code I have. Which seems to work for Pivot table 2 but not sure how to "add" pivot table 3. And as you can see if only when filtered by Country. How do I also add the filter for Dispenser type.

    sorry for the questions, fairly new VBA User here.


    Sub Pivot_Filter()

    Application.ScreenUpdating = False

    Dim PI As PivotItem

    With Worksheets("Summary Pivots").PivotTables("PivotTable2").PivotFields("Country")
    .ClearAllFilters

    Worksheets("Summary Pivots").PivotTables("PivotTable2").RefreshTable

    For Each PI In .PivotItems
    PI.Visible = WorksheetFunction.CountIf(Range("B:B"), PI.Name) & gt
    Next PI

    End With

    Worksheets("Summary Pivot").PivotTables("PivotTables1").RefreshTable

    End Sub

    Wednesday, August 24, 2016 3:28 PM

Answers

  • Hello,

    I changed the VBA slightly to the following which works for "Country" perfectly, but im still at a total loss as to how to do exactly the same but for "Dispenser Type". This new VBA is done off of a drop down cell (C2 "SelRegion") which is Data validated to a list of the countries. Whenever this drop down is changed it automatically changes all the pivots (all will do for as many pivots as I need on that one worksheet.

    Can someone please help as to how I add another code to pick cell C3 ("SelDis") for dispenser type?

    Thank so much!

    • Marked as answer by InzieBear Wednesday, August 31, 2016 11:34 AM
    Friday, August 26, 2016 7:36 AM

All replies

  • Hi InzieBear,

    >> Which seems to work for Pivot table 2 but not sure how to "add" pivot table 3.

    Did your code work for Pivot Table2? If it does, I think you could apply these code to Pivot Table 3 by replacing “PivotTable2” with “PivotTable3”.

    >> How do I also add the filter for Dispenser type.

    You could apply filter on PivotFields DispenserType. You could loop through PivotField.PivotItems, and set PivotItem.Visible Property with True or False.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, August 25, 2016 5:17 AM
  • Hi there,

    I initially tried just replacing 2 with 3 but it didn't work :(

    I know there is currently only 3 pivot tables but we want to get the VBA to work for future when we have significantly more pivot tables.

    Im also not familiar with loops? How do these work?

    Thanks

    Lyndsay

    Thursday, August 25, 2016 7:46 AM
  • Hello,

    If you want to loop your pivot's, try:

     Dim pt As PivotTable, ws As Worksheet
        
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                If pt.Name <> "MasterPivot" Then
                       'your code
                End If
            Next
        Next

    I would call your 1st pivot "MasterPivot" , so the code doesn't touch this one as it is your reference.

    Also, try to refresh your pivottables when the application opens (or better have them refreshed before, so the user is not disturbed) and don't refresh them all the time.

    Hope it helps

    Thursday, August 25, 2016 2:45 PM
  • Hello,

    I changed the VBA slightly to the following which works for "Country" perfectly, but im still at a total loss as to how to do exactly the same but for "Dispenser Type". This new VBA is done off of a drop down cell (C2 "SelRegion") which is Data validated to a list of the countries. Whenever this drop down is changed it automatically changes all the pivots (all will do for as many pivots as I need on that one worksheet.

    Can someone please help as to how I add another code to pick cell C3 ("SelDis") for dispenser type?

    Thank so much!

    • Marked as answer by InzieBear Wednesday, August 31, 2016 11:34 AM
    Friday, August 26, 2016 7:36 AM
  • Hi InzieBear,

    >> Which seems to work for Pivot table 2 but not sure how to "add" pivot table 3. And as you can see if only when filtered by Country. How do I also add the filter for Dispenser type.

    There are two issues in your thread, first is related with add filter to pivot table 3, and second is filter for Dispenser type. It seems you have resolved your first issue, am I right? If so, for the first issue, I would suggest you mark the helpful reply as answer, for the second issue, I would suggest you post a new thread, and share us more information about this, a simple demo would be helpful.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 29, 2016 5:23 AM