[VBA] PivotField.Current Page - filter on MULTIPLE items

[ChickenTenderer]

Hello world,

I have a sheet containing a bunch of PivotTables and graphs. The sheet has an area (B2:F2) where users can input filter criteria which when applied, will automatically filter the graphs displayed to the user.

For my data, there are literally tens of thousands of different PivotItems to choose from. Usually traditional methods such as looping through and marking the visible field as true or false for each one takes too long and is a terrible user experience.

I've been looking to use PivotField.CurrentPage which appears to work quicker, but can only seem to accept one PivotItem instead of multiple which in my case is the value in F2. I would really appreciate any assistance on this, as this is the last hurdle standing in the way of this sheet being complete.

Thanks in advance!

Note: the main part of the code I need help with is at the bottom in the IF statement.

Code:

Sub ApplyFilter()

    Dim PivotTableArray() As Variant
    Dim PT          As PivotTable
    Dim PTField       As PivotField
    Dim PortFilters()  As Variant
    
    
    'POPULATE PIVOTTABLE ARRAY
    PivotTableArray = Array(Worksheets("MAIN").PivotTables("AppPTBytes"), Worksheets("MAIN").PivotTables("AppPTCount"))
    
    'B2:F2 ARE RANGES FOR USERS TO ADD THEIR FILTERING CRITERIA
    PortFilters = Array(Worksheets("MAIN").Range("B2").Text, Worksheets("MAIN").Range("C2").Text, Worksheets("MAIN").Range("D2").Text, Worksheets("MAIN").Range("E2").Text, Worksheets("MAIN").Range("[B]F2[/B]").Text)
    
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Application.EnableEvents = False    
    
    
    
    For Each pvtTable In PivotTableArray
        
        'DESTINATION PORT FILTERS
        Set PTField = pvtTable.PivotFields("Destination Port")
        
        PTField.ClearAllFilters
        
        For Each port In PortFilters
           'G2 does a COUNTA statement to check if the user has added any filter criteria on the worksheet. If it's empty, then display all items in the filter
           If Range("G2").Value = 0 Then
               PTField.CurrentPage = "(All)"
           Else
               PTField.CurrentPage = port
           End If
    Next port



    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[ChickenTenderer]

Note: "F2" was my attempt at making F2 bold in the code.
The code is meant to read: Range("F2").Text

 

[Domenic]

Try setting the ManualUpdate property of the PivotTable object to True before filtering, and then setting it back to False after filtering. I haven't tested it, but I have amended your macro accordingly. Also, I have made some other changes as follows...

1) Added Option Explicit at the top of the module. This forces the explicit declaration of variables, and helps to catch errors.

2) Used the more efficient With/End With statement to assign the filtering array to PortFilters.

3) Used the more efficient IsError(Application.Match(...)) to test for a match in the criteria.

Here's the macro...

Option Explicit

Sub ApplyFilter()

    Dim PivotTableArray()   As Variant
    Dim pvtTable            As Variant
    Dim PT                  As PivotTable
    Dim PTField             As PivotField
    Dim PTItem              As PivotItem
    Dim PortFilters()       As Variant
    
    
    'POPULATE PIVOTTABLE ARRAY
    PivotTableArray = Array(Worksheets("MAIN").PivotTables("AppPTBytes"), Worksheets("MAIN").PivotTables("AppPTCount"))
    
    'B2:F2 ARE RANGES FOR USERS TO ADD THEIR FILTERING CRITERIA
    With Worksheets("MAIN")
        PortFilters = Array(.Range("B2").Text, .Range("C2").Text, .Range("D2").Text, .Range("E2").Text, .Range("F2").Text)
    End With
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Application.EnableEvents = False
    
    For Each pvtTable In PivotTableArray
    
        pvtTable.ManualUpdate = True
        
        'DESTINATION PORT FILTERS
        Set PTField = pvtTable.PivotFields("Destination Port")
        
        PTField.ClearAllFilters
        
        'G2 does a COUNTA statement to check if the user has added any filter criteria on the worksheet. If it's empty, then display all items in the filter
        If Range("G2").Value = 0 Then
            PTField.CurrentPage = "(All)"
        Else
            For Each PTItem In PTField.PivotItems
                If IsError(Application.Match(PTItem.Name, PortFilters, 0)) Then
                    PTItem.Visible = False
                End If
            Next PTItem
        End If

        pvtTable.ManualUpdate = False
        
    Next pvtTable
          
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Does this help?