Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Same Filter For Different Pivots

  1. #1
    Join Date
    3rd April 2008
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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

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

    Then in a standard module
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    3rd April 2008
    Posts
    2

    Re: Same Filter For Different Pivots

    Carl,


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

    Best Regards,

    Francisco

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Same Filter For Different Pivots

    Glad it worked out.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: February 24th, 2011, 00:16
  2. Auto Page Field Changing In Pivots
    By andyboardman in forum Excel General
    Replies: 12
    Last Post: September 15th, 2006, 15:54
  3. Replies: 11
    Last Post: October 25th, 2003, 06:19
  4. Linking Pivots [SOLVED]
    By Gerry in forum Excel General
    Replies: 3
    Last Post: April 3rd, 2003, 17:01

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
  •  
porno