Filter multiple Pivots on same worksheet based on cell in that worksheet

[manookin]

I am sure this is a simple fix but I am hoping you can help.

I do not know VBA, but am trying my hand at altering some code i found online. (see How to filter Pivot table based on a specific cell value in Excel? (extendoffice.com).

I was able to modify it to get it to work for 1 Pivot table (See code below) but I am trying to create a dashboard that will have several pivot tables/pivot charts. So I want the code below to update all the pivot tables/charts I put on that workhseet, based on a value in cell B2 of the Dashboard sheet. I am unable to figure out how to get the code to look at more than 1 pivot table. Can anyone help?



Private Sub Worksheet_Change(ByVal Target As Range)

Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String

On Error Resume Next
If Intersect(Target, Range("B1:B1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False

For Each
Set xPTable = Worksheets("Dashboard").PivotTables("PivotTable9")
Set xPFile = xPTable.PivotFields("Activity")

xStr = Worksheets("Dashboard").Cells(1, 2)
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True

End Sub

 

[manookin]

the "For each" should not be there. My apologies to whomever reads the code i was playing with something and forgot to take it out. Code should be as follows

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String

On Error Resume Next
If Intersect(Target, Range("B1:B1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False

Set xPTable = Worksheets("Dashboard").PivotTables("PivotTable9")
Set xPFile = xPTable.PivotFields("Activity")

xStr = Worksheets("Dashboard").Cells(1, 2)
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True

End Sub

 

[mart37]

For Each xPTable In Activesheet.PivotTables
'your code
next