VBA pivot table filter change based on cell value

Question

I have a pivot table "IncTrend" with 2 filters. Both of the filters I have renamed "Service1" and "Service2" (They were originally both called Service when the table was created from powerpivot)

In A2 of the worksheet I have a name of a service validation list with names of the items in the filter (they match exactly).

I am attempting to write a VBA code that will read in the "Choice" from A2, and change the pivot table filter "Service1" & "Service2" to match it.

The premise is that I will have many different pivot tables with different data that I need to change the filter to match, but I cannot even get the one to change using VBA.

I keep getting

Unable to get the PivotFields property of the PivotTable class

Any assistance would be greatly appreciated. Here is an SS of the Pivot table.

My code is:

Sub Filter_Change()

Dim WS As Worksheet: Set WS = ActiveWorkbook.Worksheets("Main")
Dim PT1 As PivotTable: Set PT1 = WS.PivotTables("IncTrend")
Dim PF1 As PivotField: Set PF1 = PT1.PivotFields("[Inc Open].[Service].[Service]")
Dim Choice As String: Choice = Worksheets("Main").Range("A2").Value

With PF1
    .ClearAllFilters
    .CurrentPage = Choice
    '.PivotFilters.Add Type:=xlCaptionEquals, Value1:=Choice
End With
End Sub

Solution 1

Three years late but maybe this helps someone trying to find an answer to the same question.

Below is the code that finally worked for my similar problem. I ended up adding my code directly to the specific sheet module and added that "If Intersect" line. Apart from that this should also work as a public sub in a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Intersect(Target, Range("A2:A3")) Is Nothing Then Exit Sub

Dim PT1 As PivotTable
Dim PF1 As PivotField
Dim Choice As String

Set PT1 = Worksheets("Main").PivotTables("IncTrend")
Set PF1 = PT1.PivotFields("[Inc Open].[Service].[Service]")
Choice = Worksheets("Main").Range("A2").Value

With PT1
  PF1.CurrentPageName = "[Inc Open].[Service].&[" & Choice & "]"
End With

End Sub

Solution 2

Replace your line :

PF1.CurrentPage.Name = Choice     

With:

With PF1
    .ClearAllFilters
    .CurrentPage = Choice
End With