Hello
i have a 2 sheets.
the first contain the main pivot table and the second contain many pivot table.
all the pivot table created from the same data base.
i want to filter fields in the main pivot table and after that all the other pivot table will be filtered with same value as the main pivot.
i succeed to find a code to do want i want, but the problem is the the filter works only for one value and i need to choose multiple values.
can some help me to solve the problem
thank you
[VBA]
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim Ws As Worksheet
Dim wsMain As Worksheet
Dim ptMain As PivotTable
Dim Pt As PivotTable
Dim pfMain As PivotField
Dim Pi As PivotItem
Dim pf As PivotField
On Error Resume Next
Set wsMain = Sheets("Sales")
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> wsMain.Name Then
For Each Pt In Ws.PivotTables
Pt.RefreshTable
For Each pf In Pt.PageFields
If pf.Name = pfMain.Name Then
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
End If
For Each Pi In pf.PivotItems
If Pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = Pi.Name
Exit For
End If
Next Pi
End If
Next pf
Next Pt
End If
Next Ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/VBA]
i have a 2 sheets.
the first contain the main pivot table and the second contain many pivot table.
all the pivot table created from the same data base.
i want to filter fields in the main pivot table and after that all the other pivot table will be filtered with same value as the main pivot.
i succeed to find a code to do want i want, but the problem is the the filter works only for one value and i need to choose multiple values.
can some help me to solve the problem
thank you
[VBA]
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim Ws As Worksheet
Dim wsMain As Worksheet
Dim ptMain As PivotTable
Dim Pt As PivotTable
Dim pfMain As PivotField
Dim Pi As PivotItem
Dim pf As PivotField
On Error Resume Next
Set wsMain = Sheets("Sales")
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> wsMain.Name Then
For Each Pt In Ws.PivotTables
Pt.RefreshTable
For Each pf In Pt.PageFields
If pf.Name = pfMain.Name Then
If pfMain.CurrentPage = "(All)" Then
pf.CurrentPage = "(All)"
Exit For
End If
For Each Pi In pf.PivotItems
If Pi.Name = pfMain.CurrentPage Then
pf.CurrentPage = Pi.Name
Exit For
End If
Next Pi
End If
Next pf
Next Pt
End If
Next Ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/VBA]
