+ 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
    02-23-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    1

    Synchronizing Two Pivot Tables of same data with One Report Filter

    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

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

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

    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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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