PDA

View Full Version : Filtering many pivot tables based on one pivot table



tommy1234
08-20-2011, 05:09 AM
Hello
i need to filter many pivot tables based on one pivot table.
i found in the internet the answer, but unfortunately the code deals only one value selection and i want to be able to choose many values.
Can someone help me to fix the code in a way that it will deals many values
selection ? Can it fixed to work faster ?
Thank you

Option Explicit
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