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

Are you sure your PivotField name is spelled exactly "Service1" ? without a space in between or something ? — Shai Rado
I modified it to be sure that it was not causing the problem. I have edited the code above showing your suggestion and my changes. — Fitzy

eg47411 eg47411 · Accepted Answer · 2020-09-06T10:38:11

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

VBA pivot table filter change based on cell value

2 Answers