Pivot table VBA macro that works on both OLAP and standard pivot tables while running from personal.xlsb

[insaneoctane]

I keep getting handed pivot tables to work with- sometimes regular, sometimes OLAP. One thing in common is they always have a column of data that I need to filter with the same values. I had an idea to write a VBA macro in my personal.xlsb file so it would always be accessible to me as I open yet another pivot table file and need to filter the data. I wanted to put the cursor (activecell) in the pivot field that contains my filter list since the name of the pivotfield would always be different from table to table. I wanted to use the activecell to identify the pivotfield in question and then filter from my pre-determined list (see FilterArray below). I wrote it, initially for regular pivot data below, but then learned on my first try that I was also receiving OLAP data and it didn't work. Can I get some help on making the below macro work for either standard or OLAP as described?

Sub Pivot_filterCC()
    Dim PT As PivotTable
  
    FilterArray = Array("42633", "42614", "42612")

    Dim myPivotField As PivotField

    Set myPivotField = ActiveCell.PivotField
    myPivotField.ClearAllFilters
    myPivotField.EnableMultiplePageItems = True
  
    numberOfElements = UBound(FilterArray) - LBound(FilterArray) + 1
  
    If numberOfElements > 0 Then
        With myPivotField
            For i = 1 To myPivotField.PivotItems.Count
                j = 0
                Do While j < numberOfElements
                    If myPivotField.PivotItems(i).Name = FilterArray(j) Then
                        myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = True
                        Exit Do
                    Else
                        myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = False
                    End If
                    j = j + 1
                Loop
            Next i
        End With
    End If

End Sub

FWIW- My actual FilterArray will actually contain 10 items (lots of clicks when doing manually) and the selection to choose from has thousands of options (making finding them difficult), hence why I'm looking to automate!

 

[Alex Blakenburg]

You're welcome. I learnt some new things too. Glad I could help.