I have an Excel macro that contains a pivot table. The macro has to read a worksheet and eliminate any line with a code that is not in the following list (1,5, 12, 7, 20). This part works good.

After the data is finalized, I’m expecting the pivot table to be recreated. When I look at the filter drop down, all of the codes are there but none of them are checked. As a result, the pivot table does not contain any data and the macro cannot complete the rest of the code since the pivot able is blank.

ActiveSheet.PivotTables("PivotTable2").PivotFields("Earning Reference Code"). _
        CurrentPage = "(All)"
    
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
     "Earning Reference Code")
     .PivotItems("(blank)").Visible = False
     .PivotItems("208").Visible = True
     .PivotItems("213").Visible = True
     .PivotItems("184").Visible = True
     .PivotItems("279").Visible = True
     .PivotItems("195").Visible = True
     .PivotItems("161").Visible = True
     .PivotItems("236").Visible = True
     .PivotItems("190").Visible = True
     .PivotItems("220").Visible = True
     .PivotItems("225").Visible = True
End With

I get an error message when it tries to set the 1st value to True. (I’ve also used a variable for the filter name.) The message says “Unable to get the PivotItems property of the PivotField class”.

How do I check all of the filter options each time this macro is run? It has to be dynamic because the final data may not have the same codes each time the macro is run.

Thanks for your help in advance. I appreciate your time and consideration with my issue……..