Filter Pivot Table Based On Selected Value And Show All Results Though VBA

[hanishgautam]

Hi All,

Please find the excel file in which you can filter your result in pivot table according to dropdown selection and find the code give below:

Sub ShowAll()

    Dim pt As PivotTable, pi As PivotItem
    Dim xlCalc As XlCalculation
    
    Set pt = Sheet1.PivotTables("PivotTable1")
    
    pt.ManualUpdate = True

    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False '
    End With
    
    On Error Resume Next
    
    For Each pi In pt.PivotFields("StaffCode").PivotItems
        pi.Visible = True
    Next pi
    
    On Error GoTo 0

    pt.ManualUpdate = False

    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
    End With

End Sub


Sub ShowPivotFilterResult()

    Dim pt As PivotTable, pi As PivotItem
    Dim xlCalc As XlCalculation
    
    
    Set pt = Sheet1.PivotTables("PivotTable1")
    
    pt.ManualUpdate = True
    
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False '
    End With
    
    On Error Resume Next
    
    For Each pi In pt.PivotFields("StaffCode").PivotItems
        pi.Visible = True
    Next pi
    
    On Error GoTo 0


    On Error Resume Next
    
    For Each pi In pt.PivotFields("StaffCode").PivotItems
        If pi.Value = Range("rngVALUE").Value Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next pi
    
    On Error GoTo 0

    pt.ManualUpdate = False

    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
    End With

End Sub

[Excel Fox]

Thanks Hanish. You can wrap your code using the code tags. To use code tags, either select your code and press the code tag button in the editor group, or type [Code]your code here[\Code] and use a forward slash instead of backward.

[hanishgautam]

Sure I will remember next time whenever i will post...

[snb]

This is a nonsense item.
Filtering can be accomplished in a table (listobject).
The sum of ID's is nonsense.

[Excel Fox]

snb, I tend to agree to your point (or concern, rather) regarding the above post, particularly since the pivot table is almost identical to a listobject on a sheet, and to filter a listobject, it doesn't take elaborate coding. I guess hanishgautam was just trying to showcase how one can filter a pivot table / pivot item.

In addition, I agree that Sum of ID doesn't feel logical at all. Again, the intent was probably just what I presumed above. Having said that, I would put it this way - hanishgautam could have used a more apt data example to showcase what he was trying to.

Let's just call it fair for this one.

hanishgautam, snb is a very skilled and admirable person among such forums, and I am sure you'll appreciate the gravity of snb's mind in this above post of yours. Let's just say snb likes to run a tight ship when it comes to coding and applying it to scenarios.

Cheers gentlemen.