Run a code section for 2 Pivot tables.

[lemnguyen]

Hello,

I just found this code for my purpose, but this code only run one time for filtering one pivot table, but i want to run it for 2 pivot tables (from different sheet, different source). What should i add more code into this code section?
In this code, it run for Worksheet "Tháng M-1" and Pivottable "Checkshop_M-1", but i also want that it run for Worksheet "Tháng M" and Pivottable "Checkshop_M" with the same reference value in A1 cell as well.

---------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Tháng M-1").PivotTables("Checkshop_M-1")
Set xPFile = xPTable.PivotFields("Tên Shop")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub
--------------------------------------

 

[Alex Blakenburg]

Based on where you are at with VBA it probably easiest to just repeat the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    
    ' Pivot table 1
    Set xPTable = Worksheets("Tháng M-1").PivotTables("Checkshop_M-1")
    Set xPFile = xPTable.PivotFields("Tên Shop")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    
    ' Pivot table 2
    Set xPTable = Worksheets("Tháng M").PivotTables("Checkshop_M")
    Set xPFile = xPTable.PivotFields("Tên Shop")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
      
    Application.ScreenUpdating = True
End Sub

 

[lemnguyen]

Private Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 20180702 Dim xPTable As PivotTable Dim xPFile As PivotField Dim xStr As String On Error Resume Next If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Application.ScreenUpdating = False ' Pivot table 1 Set xPTable = Worksheets("Tháng M-1").PivotTables("Checkshop_M-1") Set xPFile = xPTable.PivotFields("Tên Shop") xStr = Target.Text xPFile.ClearAllFilters xPFile.CurrentPage = xStr ' Pivot table 2 Set xPTable = Worksheets("Tháng M").PivotTables("Checkshop_M") Set xPFile = xPTable.PivotFields("Tên Shop") xStr = Target.Text xPFile.ClearAllFilters xPFile.CurrentPage = xStr Application.ScreenUpdating = True End Sub

Dear Alex,

It works perfectly!

Many thanks.

 

[Alex Blakenburg]

No problem. Glad I could help. Thanks for letting me know.