Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Same Filter For Different Pivots

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Same Filter For Different Pivots

    Hello all,

    I have two pivots tables in two different worksheets with the same reports filters. These pivots tables are getting information from a cube in an SQL server.

    Here's the deal, I would like to be able of changing one filter in one pivot and then automatically the same filter in the second pivot would change equally.

    Thanks all for your time and help,

    Francisco

  • #2
    Re: Same Filter For Different Pivots

    Francisco,

    Programming inside a PT connected to an SQL OLAP cube can be tricky because you move between normal Pivot Fields and the concept of Cube Fields. I think just to sync two Page Fields in two PTs you can keep it simple and copy the current page name accross .

    You could use the PT update event in the first worksheet

    Try

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Call SyncPivots
    End Sub

    Then in a standard module
    Code:
    Public Sub SyncPivots()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim PT1 As PivotTable
    Dim PT2 As PivotTable
    Dim PF As PivotField
    Set ws1 = ThisWorkbook.Worksheets("YourFirstSheetName")
    Set ws2 = ThisWorkbook.Worksheets("YourSecondSheetName")
    Set PT1 = ws1.PivotTables("FirstPTName")
    Set PT2 = ws2.PivotTables("SecondPTName")
    For Each PF In PT1.PivotFields
        If PF.Orientation = xlPageField Then
            Debug.Print PF.CurrentPageName
            'On Error Resume Next ' comment in at the end (in case PTs are not the same)
            PT2.PivotFields(PF.Name).CurrentPageName = PF.CurrentPageName
        End If
    Next PF
    
    End Sub




    HTH
    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Same Filter For Different Pivots

      Carl,


      Thank you very much. It was really helful and it worked!

      Best Regards,

      Francisco

      Comment


      • #4
        Re: Same Filter For Different Pivots

        Glad it worked out.
        locii Insight
        Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X