Automatically filter 2 filters in pivot table with 2 cell values

MPaul100

New Member
Joined
Aug 21, 2019
Messages
24
Hi, I am using the code below to filter a pivot table based on a cell value that is using a dropdown. It works properly, but I cannot find a solution to use another cell value in another filter. Basically, the process would be: change cell C3, filter first field, change O3, filter second field.
If I am using a single criteria it works, but if I add another criteria it doesn't work. It throws an error on the line marked in yellow. I hope that someone has an idea on how to fix this.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("C3:O3")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field1 As PivotField
Dim Field2 As PivotField
Dim NewCat1 As String
Dim NewCat2 As String

'Here you amend to suit your data
Set pt = Worksheets("Obj ").PivotTables("PivotTable3")
Set Field1 = pt.PivotFields("Eq")
Set Field1 = pt.PivotFields("Ach")
NewCat1 = Worksheets("Obj ").Range("C3").Value
NewCat2 = Worksheets("Obj ").Range("O3").Value

'This updates and refreshes the PIVOT table
With pt
Field1.ClearAllFilters
[COLOR=rgb(250, 197, 28)]Field1.CurrentPage = NewCat1[/COLOR]
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
pt.RefreshTable
End With

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top