VBA to Deselect PivotFields Values if it exists

[th259]

I have a pivot table called Fiscal53_Data. One PivotFields, the "Check#" is already filtered to show only total check greater than $10K. From the same pivot table, I want to deselect Vendor Names, "Bank of Marin" and "Bank of Mellon" if it exists. I got the following code but the code for deselecting "Bank of Marin" actually makes the pivot table disappeared. How do I fix the code below?

With ActiveSheet.PivotTables("Fiscal53_Data").PivotFields("Total")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0.00"                                                               'Format Number
    .Name = "Sum of Total"
'Pivot Table Tabular View
    With ActiveSheet.PivotTables("Fiscal53_Data")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("Fiscal53_Data").PivotFields("Check#").PivotFilters. _
        Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
        "Fiscal53_Data").PivotFields("Sum of Total"), Value1:=10000                          'Filter Check# Column that has a total bigger than $10K
    Columns("D:D").Select
    Selection.Replace What:="(blank)", REPLACEMENT:="Direct Pay", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        End With
    Dim pt As PivotTable, pi As PivotItem
    For Each pt In Data_Pivot.PivotTables
    If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
        For Each pi In pt.PivotFields("Vendor Name").PivotItems
            If pi.Name = "BANK OF MARIN" Then                          'check that item name exists
                pi.Visible = False: Exit For                                         'Do not show item if it exists
        End If
        Next pi: Exit For
        End If
Next pt

 

[DanteAmor]

I tested your code and it works to hide an item.

The only strange thing I noticed is that in this line you have the Data_Pivot object, but I don't see the declaration of the object, it seems strange to me, because in the previous references, it refers to Activesheet, but here it mentions an object.

For Each pt In Data_Pivot.PivotTables

Check if that object is for the same active sheet.

 

[th259]

I got it to work thank you very much! I got the following codes to deselect 3 items if it exists, is there a way to condensed the code? Make the 3 items into an array?

Dim pt As PivotTable, pi As PivotItem
    For Each pt In ActiveSheet.PivotTables
    If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
        For Each pi In pt.PivotFields("Vendor Name").PivotItems
            If pi.Name = "HAYWARD UNIFIED SCHOOL DIST" Then                          'check that item name exists
                pi.Visible = False: Exit For                                         'Do not show item if it exists
        End If
        Next pi: Exit For
    End If
        Next pt
    For Each pt In ActiveSheet.PivotTables
    If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
        For Each pi In pt.PivotFields("Vendor Name").PivotItems
            If pi.Name = "BANK OF MARIN" Then                          'check that item name exists
                pi.Visible = False: Exit For                                         'Do not show item if it exists
        End If
        Next pi: Exit For
        End If
        Next pt
        For Each pt In ActiveSheet.PivotTables
        If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
        For Each pi In pt.PivotFields("Vendor Name").PivotItems
            If pi.Name = "HAYWARD UNIFIED SCHOOL DIST" Then                          'check that item name exists
                pi.Visible = False: Exit For                                         'Do not show item if it exists
        End If
        Next pi: Exit For
        End If
        Next pt
    For Each pt In ActiveSheet.PivotTables
    If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
        For Each pi In pt.PivotFields("Vendor Name").PivotItems
            If pi.Name = "BANK OF NEW YORK MELLON" Then                          'check that item name exists
                pi.Visible = False: Exit For                                         'Do not show item if it exists
        End If
        Next pi: Exit For
        End If
        Next pt

 

[th259]

Upping this thread!

Is there a way to condense the code above, instead of repeating the same code over again to search for new values to deselect, can I create a array of values to deselect if it exists?

Thank you in advance!

 

[Alex Blakenburg]

I don't have any data to test this with but see if this works for you.

    Dim pt As PivotTable, pi As PivotItem
    Dim arrFltr As Variant
    
    arrFltr = Array("HAYWARD UNIFIED SCHOOL DIST", _
                    "BANK OF MARIN", _
                    "HAYWARD UNIFIED SCHOOL DIST", _
                    "BANK OF NEW YORK MELLON")
    
    For Each pt In ActiveSheet.PivotTables
        If pt.Name = "Fiscal53_Data" Then                                                        'check that pivot name exists
            For Each pi In pt.PivotFields("Vendor Name").PivotItems
                If Not IsError(Application.Match(pi.Name, arrFltr, 0)) Then                         'check that item name exists
                    pi.Visible = False                                                     'Do not show item if it exists
            End If
            Next pi: Exit For
        End If
    Next pt

 

[th259]

It worked perfectly, thank you very much!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.