0

I tried using the code that appeared to have helped the poster at

Excel VBA - Privot table filter multiple criteria

But I keep on getting the following error: Unable to get the PIvtoFields property of the PIvotTable Class.

Sub FilterPivotItems()

    Dim PT          As PivotTable
    Dim PTItm       As PivotItem
    Dim FiterArr()  As Variant

    ' use an array to select the items in the pivot filter you want to keep visible
    FiterArr = Array("MC. Santa Clara", "MC. Plaza Américas", "MC. El Frutal")

    ' set the Pivot Table
    Set PT = ActiveSheet.PivotTables("PivotTable4")

    ' loop through all Pivot Items in "Value" Pivot field
    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

What can I do?

Automate This
  • 30,726
  • 11
  • 60
  • 82
Victor Aguirre Vargas
  • 1

1 Answers1

0

After reading about the properties of the class, I finally got to what I wanted.

Sub filterpivot()


Dim pi As PivotItem
Dim pt As PivotTable
Dim first As String
Dim second As String
Dim third As String
Dim fourth As String

first = Range("a26").Value
second = Range("a27").Value
third = Range("a28").Value
fourth = Range("a29").Value

Set pt = Worksheets("Lowest Scores").PivotTables("PivotTable4")

    With pt.PivotFields("Nombre conjunto")
        For Each pi In pt.PivotFields("Nombre conjunto").PivotItems
        
        
            If pi.Name = first Or pi.Name = second Or pi.Name = third Or pi.Name = fourth Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        Next pi
    End With


End Sub

This bases the applied filter on specific cell values. My apologies if anyone was working on this already, I was on a crunch at work and needed the answer.

Victor Aguirre Vargas
  • 1