Pivot multiple filter VBA code

[Samer malik]

Hi All,

new here to the forum and was looking for abit of help i am fairly new to VBA and was wondering if someone could help with the solution to the following query:

I have the below code to filter a pivot table using a specific cell reference in this case cell B3 in the worksheet "Front Sheet" the issue i have is that the pivot table has 2 fields i want to filter this way how do i add a second criteria ?



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("b3:b6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Pivot").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Division")
NewCat = Worksheets("Front Sheet").Range("B3").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

End With

End Sub

this works for the pivot field criteria Division, but i would like to get it to work for 2 pivot fields names Division referencing cell B3 and Categories referencing cell B5.

any help would be greatly appreciated.

 

[Worf]

Welcome

Are both fields in the filters area?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("b3:b6")) Is Nothing Then Exit Sub
Dim pt As PivotTable, Field As PivotField
Set pt = Worksheets("Pivot").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Division")
With pt
    .ClearAllFilters
    Field.CurrentPage = Sheets("front sheet").Range("b3").Value
    .PivotFields("Categories").CurrentPage = Sheets("front sheet").Range("b5").Value
    .RefreshTable
End With
End Sub