I am really new to VBA and I am trying to change the filter of multiple pivot tables by changing a single cell. I have the following code set up to change the filter in a single pivot table but need help with the code to add more tables:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("E1:F1")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("Sheet2").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Subsidiary")
NewCat = Worksheets("Sheet2").Range("E1").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Any help is greatly appreciated