Filter multiple items in Pivot

Sub FilterPivotItems()


Dim PT          As PivotTable

Dim PTItm       As PivotItem

Dim FiterArr()  As Variant


' use this array variable to select the items in the pivot table filter which you want to keep visible

FiterArr = Array("101", "105", "107")


' set the Pivot Table

Set PT = Sheet1.PivotTables("PivotTable1")


' loop through all Pivot Items in "Value" field of the Pivot

For Each PTItm In PT.PivotFields("Value").PivotItems

    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array

        PTItm.Visible = True

    Else

        PTItm.Visible = False

    End If

Next PTItm


End Sub

एक टिप्पणी भेजें

0 टिप्पणियाँ