+ Reply to Thread
Results 1 to 2 of 2

Synchronizing Two Pivot Tables of same data with One Report Filter

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Synchronizing Two Pivot Tables of same data with One Report Filter

    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
    .CurrentPage = "(All)"
    End If
    Next pi
    End With
    Next pt
    Next ws

    End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016

    Re: Synchronizing Two Pivot Tables of same data with One Report Filter


    The following code from Debra Dalgleish will do what you want.
    You can download an example workbook from her site at

    Please Login or Register  to view this content.
    Roger Govier
    Microsoft Excel MVP

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1