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