CODEWITHSUNDEEP
excelvba
evob20
evob20

Reputation: 1

VBA to select all and deselect 0 & blank on filter for pivot table across multiple sheets

The VBA below selects all and deselects 0 and blanks on a pivot table filter, essentially refreshing the pivot table after new data is entered. It works correctly on a single sheet but the issue I have is that PivotTable1 is copied across multiple sheets and I also want this to run this on them pivot tables.

I have tried to use an array to no avail and I'm too much of a rookie to figure out how to get this to continue the same VBA onto the next sheet/pivot table.

Option Explicit

Public Sub FilterOutZeroAndBlanks()

    Dim pvt As PivotTable

    Set pvt = ThisWorkbook.Worksheets("Cairns Table").PivotTables("PivotTable1")
    Dim pvtField As PivotField
    Set pvtField = pvt.PivotFields("Quantity")

    Dim item As PivotItem
    Dim counter As Long
    Dim targetCounter As Long

    With pvtField

       For Each item In .PivotItems

           If item.Visible Then counter = counter + 1

       Next item

       If .PivotItems("0").Visible And .PivotItems("(blank)").Visible Then
            targetCounter = 2
       ElseIf .PivotItems("0").Visible Or .PivotItems("(blank)").Visible Then
            targetCounter = 1
       End If

       If Not targetCounter = counter Then

           .PivotItems("0").Visible = False
           .PivotItems("(blank)").Visible = False

       End If


    End With

End Sub

Upvotes: 0

Views: 1301

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

Make the pivottable a parameter - then you can more-easily re-use the method by calling it from another sub:

Sub Main()

    With ThisWorkbook
        FilterOutZeroAndBlanks .Worksheets("Cairns Table").PivotTables("PivotTable1")
        FilterOutZeroAndBlanks .Worksheets("Other Table").PivotTables("PivotTable1")
    End With

End Sub

Public Sub FilterOutZeroAndBlanks(pvt As PivotTable)

    Dim pvtField As PivotField
    Set pvtField = pvt.PivotFields("Quantity")
    
    Dim item As PivotItem
    Dim counter As Long
    Dim targetCounter As Long
    
    With pvtField
        For Each item In .PivotItems
            If item.Visible Then counter = counter + 1
        Next item
        
        If .PivotItems("0").Visible And .PivotItems("(blank)").Visible Then
            targetCounter = 2
        ElseIf .PivotItems("0").Visible Or .PivotItems("(blank)").Visible Then
            targetCounter = 1
        End If
        
        If Not targetCounter = counter Then
            .PivotItems("0").Visible = False
            .PivotItems("(blank)").Visible = False
        End If
    End With
End Sub

Upvotes: 0

Related Questions