If I could have your help for excel VBA.

Contexte

I have created two dynamic tables on the same sheet names are : "Tableau croisé dynamique1" and "Tableau croisé dynamique4" I would like to use a cell reference to filter the two tables.

I found this code below that working for one table at this moment forgive me my poor Vba coding so i tried to duplicate it and change the variable.

Objectif

The objectif is to correct the code to have the two filters of the tbales working the same So if i filter for "week 26" i have the both tables showing only "week 26" datas.

Code

Private Sub Worksheet_Change(ByVal Target As Range)

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

Dim xPTable4 As PivotTable
Dim xPFile4 As PivotField
Dim xStr4 As String

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

Set xPTable4 = Worksheets("Resumen_ventas").PivotTables("Tableau croisé dynamique4")
Set xPFile4 = xPTable.PivotFields("SEMANA")

Set xPTable = Worksheets("Resumen_ventas").PivotTables("Tableau croisé dynamique1")
Set xPFile = xPTable.PivotFields("SEMANA")

xStr4 = Target.Text

xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr


xPFile4.ClearAllFilters
xPFile4.CurrentPage = xStr4
Application.ScreenUpdating = True

End Sub

Thank you !

CodePudding user response：

Excel screen shot here of the situation

the "SEMANA" field for each PivotTable must be visible on the sheet (drag it under "Filters" to make them PageFields

https://learn.microsoft.com/en-us/office/vba/api/excel.pivotfield.currentpage

You have to disable events for that, because some the ClearAllFilters() method triggers another Change event on the sheet.

Set xPFile4 = xPTable.PivotFields("SEMANA")

corrected in:

Set xPFile4 = xPTable4.PivotFields("SEMANA")

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Dim xPTable4 As PivotTable
Dim xPFile4 As PivotField
Dim xStr4 As String

On Error Resume Next

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

Set Target2 = Range("B4")

Application.ScreenUpdating = False

Set xPTable4 = Worksheets("Resumen_ventas").PivotTables("Tableau croisé dynamique4")
Set xPFile4 = xPTable4.PivotFields("SEMANA")

xStr4 = Target2.Text
Application.EnableEvents = False
    xPFile4.ClearAllFilters
Application.EnableEvents = True
xPFile4.CurrentPage = xStr4

Set xPTable = Worksheets("Resumen_ventas").PivotTables("Tableau croisé dynamique1")
Set xPFile = xPTable.PivotFields("SEMANA")

xStr = Target2.Text
Application.EnableEvents = False
    xPFile.ClearAllFilters
Application.EnableEvents = True
xPFile.CurrentPage = xStr

Application.ScreenUpdating = True

End Sub