Change Multiple Pivot Filters based on cell value

[shansakhi]

Hello Everybody,
I have below code which allows to change Pivot Filter for one pivot based on a cell value.
Can you please assist me to alter this macro to change multiple Pivots based on same cell value.
Note: All pivots are created from same database.

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("I1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("POS")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub

Regards,
Shan

 

[Misca]

Try something like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WS As Worksheet
Dim PT As PivotTable

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("I1")) Is Nothing Then Exit Sub
If Target.Text = "" Then Exit Sub

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

On Error Resume Next

    For Each WS In Worksheets
    
        For Each PT In WS.PivotTables
            With PT.PivotFields("POS")
                .ClearAllFilters
                .CurrentPage = Target.Text
            End With
        Next PT
    
    Next WS

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

 

[shansakhi]

Thank you Misca.
I used the code and have three issues.
1. It's changing pivots for all the sheets. I want to restrict with the sheet name.
2. If Target text is blank then pivot filter should be cleared as earlier code. Now it's remaining on the last Target text selected.
3. This will be another help. I tried using same code for another Target Cell and with another Pivot filter name in the same code. But while running its gives me an error.
I want to add multiple Cell Values to control pivot filters.

Regards,
Shan