Filtering Pivot table for specific cell values when number of PivotItems vary

Question

I am working on this Excel VBA script that filters Pivot table with specific values in given Pivot field.

I need this to be more dynamic. Every time my macro filters for something that is not in the current Pivot field, I get error message.

The following filtering code, works when all the .PivotItems are present in the data but this changes from data to data and I cannot type this manually every time to have it the same as in the data:

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Dispute ")
        '.PivotItems("CA - Carriage").Visible = False
        '.PivotItems("Credit").Visible = False
        .PivotItems("DOC - Documentation issue").Visible = False
        .PivotItems("Paid").Visible = False
        .PivotItems("Promise to pay").Visible = False
        '.PivotItems("QQ - Quantity Query").Visible = False
        .PivotItems("SR - Sales Related").Visible = False
        .PivotItems("(blank)").Visible = False
        .PivotItems("PT - Payment Terms").Visible = False
        .PivotItems("payment in advance").Visible = False
        .PivotItems("Bancrupcy").Visible = True
        .PivotItems("PO - Purchase Order").Visible = False
        .PivotItems("RT - Returns").Visible = False
        .PivotItems("to be reconcilied").Visible = False
        .PivotItems("Dispute - Resolved").Visible = False
        .PivotItems("Follow up ").Visible = False
        .PivotItems("escalation").Visible = False
    End With

Lets say ".PivotItems("escalation").Visible = False" is not present in the data this time, the macro then throws error when trying to filter it out.

There should be a solution to just provide ".PivotItems" I want to show in the filter and ignore the rest but so far I could not google exactly what I need.

HTH HTH · Accepted Answer · 2020-04-14T12:08:26

you could use an array to store your (un)wanted fileds into and iterate through it:

Dim fields As Variant, field As Variant

fields = Array("field1", "field2", "field8")' <-- name of the fields you want to hide
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Dispute ")
    For Each field In fields
        .PivotItems(field).Visible = False
    Next
End With

Edit after OP clarifications

or, if you already know the fields you want to be visible:

Dim fieldsIWantToShowForSure As Variant
fieldsIWantToShowForSure = Array("field1", "field2", "field8")

Dim pvtIt As PivotItem
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Dispute ")
    For Each pvtIt In .PivotItems
        If IsError(Application.Match(pvtIt.Name, fieldsIWantToShowForSure, 0)) Then pvtIt.Visible = False
    Next
End With

Filtering Pivot table for specific cell values when number of PivotItems vary

1 Answers