Pivot filter macro run from personal.xlsb

[insaneoctane]

I'm trying to create a macro that filters any pivot table I place my cursor on with a predetermined list (see FilterArray below). My code gives Run-time error '1004': Unable to get the PivotFields property of the PivotTable class when I place my cursor in the pivot field that I want to apply the filter. I know I'm close to making this work!! Any advice?

Sub Pivot_filterCC()
    Dim PT As PivotTable
    
    FilterArray = Array("42633", "42614", "42612")
      
    Dim myPivotField As PivotField
    
    Set PT = ActiveSheet.PivotTables(1)
    Set MyField = ActiveCell.PivotField
    Set myPivotField = PT.PivotFields(MyField)
    
    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

 

[Domenic]

You'll need to replace...

Set myPivotField = PT.PivotFields(MyField)

with

Set myPivotField = PT.PivotFields(MyField.Caption)

However, you can actually replace...

    Dim myPivotField As PivotField
    
    Set PT = ActiveSheet.PivotTables(1)
    Set MyField = ActiveCell.PivotField
    Set myPivotField = PT.PivotFields(MyField)

with

    Dim myPivotField As PivotField
    Set myPivotField = ActiveCell.PivotField

Hope this helps!

 

[insaneoctane]

You'll need to replace...

Set myPivotField = PT.PivotFields(MyField)

with

Set myPivotField = PT.PivotFields(MyField.Caption)

However, you can actually replace...

    Dim myPivotField As PivotField
  
    Set PT = ActiveSheet.PivotTables(1)
    Set MyField = ActiveCell.PivotField
    Set myPivotField = PT.PivotFields(MyField)

with

    Dim myPivotField As PivotField
    Set myPivotField = ActiveCell.PivotField

Hope this helps!

Thanks for the quick reply!
I made your suggested changes ("...you can actually replace...") and now my ClearAllFilters is working (progress!), but it now errors on "myPivotField.EnableMultiplePageItems = True". Seems strange that the clear filters works properly, but then it throws error 5 and when I debug, it shows <invalid procedure call or argument> on hovering on the left side of that statement. Any more advice?

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

 

[insaneoctane]

I don't know if it matters how the pivot data is setup, but when I did a macro record to view the syntax of enabling multipageitems, it recorded this:
ActiveSheet.PivotTables("PivotTable5").CubeFields(47).EnableMultiplePageItems = True

So, I'm wondering if my data utilizes this "cubefield" property that is breaking my macro. Honestly, I'd need my macro to handle various pivot data types...

 

[Domenic]

It looks like you have an OLAP pivot table. In that case, try...

myPivotField.CubeField.EnableMultiplePageItems = True

However, the rest of your code will need to be amended accordingly. I would suggest that you record a macro as you filter the pivot table, and try to amend the recorded code for your use.

If you need help, post the recorded code here, and someone will be able to clean it up for you. Actually, you might be better off starting a new thread since you actually have an OLAP pivot table, and since more people would likely see your post.

 

[insaneoctane]

It looks like you have an OLAP pivot table. In that case, try...

myPivotField.CubeField.EnableMultiplePageItems = True

However, the rest of your code will need to be amended accordingly. I would suggest that you record a macro as you filter the pivot table, and try to amend the recorded code for your use.

If you need help, post the recorded code here, and someone will be able to clean it up for you. Actually, you might be better off starting a new thread since you actually have an OLAP pivot table, and since more people would likely see your post.

Thanks for the reply. I'm interested in a macro that works in "regular" and OLAP pivot tables. I frequently receive pivot tables from various sources (hence, I don't know if it is OLAP or not). Inevitably, I need to apply the filter shown (although I really will apply 10 values to the Filter, not just 3 as shown above). The name of the field won't be the same, but the data will contain a field that contains my filter list (and thousands of others that I don't want to sift through!). So, my intent was to put my cursor in the field and run a personal.xlsb macro on the source file dejour. Any additional advice on how to update my macro much appreciated!