CODEWITHSUNDEEP
vbaexcelpivot-table
Fitzy
Fitzy

Reputation: 113

VBA pivot table filter change based on cell value

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.

enter image description here

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

Upvotes: 2

Views: 13778

Answers (2)

eg47411
eg47411

Reputation: 36

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

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

Replace your line :

PF1.CurrentPage.Name = Choice

With:

With PF1
    .ClearAllFilters
    .CurrentPage = Choice
End With

Upvotes: 1

Related Questions