Synchronizing Two Pivot Tables of same data with One Report Filter

**aravinda.kumar** · 05-31-2011, 02:49 AM

Hi,
Please help me in Synchronizing two Pivot tables of same data with One Report Filter.

I have almost done with the Following VB Code found in the Web, but as i dont know how to add some more fields in the Code.(Ex: The below code only changes my "Region" filter but i have some more filter field in my pivot table as "State", "Town".) If you would have tell me how to add more strings(Report Filter Field) in the following code will also help me in closing this.

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Region"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

**Roger Govier** · 05-31-2011, 03:11 AM

Hi

The following code from Debra Dalgleish will do what you want.
You can download an example workbook from her site at
http://www.contextures.com/PivotMultiPagesChangeAll.zip

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 Pivot")
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