How do I Filter one or multiple PivotTable Field using VBA?-CodePudding

I have a workbook with multiple pivot tables. I have been writing macros to filter a specific field ("YEAR_FW") on the pivot tables based on cell range that lives on a different sheet. After a bunch of trial and error I decided to write separate code for each Pivot table. I was able to filter most of the pivot tables but there are some that give me an error.

Here is the cell range:

enter image description here

Here are the pivot tables(the first one got filtered):

enter image description here

The first table code that worked:

Sub Filter_dip4wk()

Dim rng As Range
Set rng = Range("B3:B58")
Dim ptb As PivotTable
Set ptb = Sheets("DIP COAT TABLE 4week").PivotTables("PivotTable1")
Dim fld As PivotField
Set fld = ptb.PivotFields("YEAR_FW")
           
    With fld

    Dim Item As PivotItem

    For Each Item In .PivotItems
     Item.Visible = True
    Next Item
     
    For Each Item In .PivotItems
        Item.Visible = False
        Dim cell As Range
            For Each cell In rng
                If Item.Caption = cell.Text Then
                    Item.Visible = True
                    Exit For
                End If
            Next cell
    Next Item

    End With

End Sub

The second table code and error (basically just changing pivot table name):

Sub Filter_dip4wkt2()

Dim rng As Range
Set rng = Range("B3:B58")
Dim ptb As PivotTable
Set ptb = Sheets("DIP COAT TABLE 4week").PivotTables("PivotTable2")
Dim fld As PivotField
Set fld = ptb.PivotFields("YEAR_FW")
           
    With fld

    Dim Item As PivotItem

    For Each Item In .PivotItems
     Item.Visible = True
    Next Item
     
    For Each Item In .PivotItems
        Item.Visible = False
        Dim cell As Range
            For Each cell In rng
                If Item.Caption = cell.Text Then
                    Item.Visible = True
                    Exit For
                End If
            Next cell
    Next Item

    End With

End Sub

enter image description here

I think it is because the second pivot table has a row field. I tried making changed to PivotItem part but still got errors. Any ideas? I am new to VBA/Macros so apologies if I missed out any details.

CodePudding user response：

Try something like this:

Sub Filter_dip4wkt2()

    Dim rng As Range, fld As PivotField, itm As PivotItem
    
    Set rng = Range("B3:B58") 'ideally specify a worksheeet here...
    
    Set fld = Sheets("DIP COAT TABLE 4week").PivotTables("PivotTable2").PivotFields("YEAR_FW")
           
    fld.ClearAllFilters 'first set all visible
    
    For Each itm In fld.PivotItems
        'look for the caption in rng
        If IsError(Application.Match(itm.Caption, rng, 0)) Then
            itm.Visible = False 'hide non-matches
        End If
    Next itm
    
End Sub