Results 1 to 8 of 8

Thread: Filter Multiple Pivot Tables Having Different Sources

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter Multiple Pivot Tables Having Different Sources

    Hi allz,
    I have a workbook having multiple sheets and one sheet containing Pivot Tables connected to all other data sheets. The data have one column that is common among all data sources lets say "SP" column. I need to filter one SP value on all Pivot Tables with a single click.


    I can not use Slicers because the source of these Pivots are different and I cant merge these data in a single sheet so i can use slicers.


    I have done it with this simple macro but I need to make it variable that if i add more data and more Pivots in this sheet the macro adjusts it self and filter all Pivots based on a value in a common column i.e. SP.
    I tried to do it with getting list of all Pivots and then tried to use For loop to do it one by one on all Pivots. I got the list of all Pivots with following macro but can not proceed further because I cant get the list of all Pivot Filter Items so I can apply filter to them and my knowledge are not that as well.


    Code:
    Sub ListPivotsInfor()
        Dim St As Worksheet
        Dim NewSt As Worksheet
        Dim pt As PivotTable
        Dim I, K As Long
        Application.ScreenUpdating = False
        Set NewSt = Worksheets.Add
        I = 1: K = 2
        With NewSt
            .Cells(I, 1) = "Name"
            For Each St In ActiveWorkbook.Worksheets
                For Each pt In St.PivotTables
                    I = I + 1
                    .Cells(I, 1).Value = pt.Name
                    Next
            Next
            .Activate
        End With
        Application.ScreenUpdating = True
    End Sub

    Kindly have a look on this sheet and suggest a macro that can do the required. Thanks...!

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    Try this worksheet event procedure

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim Slicer As SlicerItem
    Dim pvt As PivotTable
    Dim pvtItem As PivotItem
    Dim values As Variant
    Dim nextrow As Long
    Dim i As Long, ii As Long
        On Error GoTo wsptu_exit
        Application.EnableEvents = False
        
        For i = 1 To Target.Slicers.Count
        
            nextrow = 1
            ReDim values(1 To nextrow)
            
            For Each Slicer In Target.Slicers(i).SlicerCache.VisibleSlicerItems
        
                ReDim Preserve values(1 To nextrow)
                values(nextrow) = Slicer.Name
                nextrow = nextrow + 1
            Next Slicer
            
            For Each pvt In Me.PivotTables
            
                With pvt.PivotFields("SP")
                
                    .PivotItems(values(LBound(values))).Visible = True
                    
                    For Each pvtItem In .PivotItems
                    
                        pvtItem.Visible = Not IsError(Application.Match(pvtItem.Value, values, 0))
                    Next pvtItem
                End With
            Next pvt
        Next i
    wsptu_exit:
        Application.EnableEvents = True
    End Sub

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    I forgot to say, add a slicer on the column SP attached tio the first pivottable, and select your chosen values from there. That is what triggers the event.
    Last edited by theBardd; May 18th, 2019 at 08:55 AM.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    Quote Originally Posted by theBardd View Post
    Try this worksheet event procedure

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim Slicer As SlicerItem
    Dim pvt As PivotTable
    Dim pvtItem As PivotItem
    Dim values As Variant
    Dim nextrow As Long
    Dim i As Long, ii As Long
        On Error GoTo wsptu_exit
        Application.EnableEvents = False
        
        For i = 1 To Target.Slicers.Count
        
            nextrow = 1
            ReDim values(1 To nextrow)
            
            For Each Slicer In Target.Slicers(i).SlicerCache.VisibleSlicerItems
        
                ReDim Preserve values(1 To nextrow)
                values(nextrow) = Slicer.Name
                nextrow = nextrow + 1
            Next Slicer
            
            For Each pvt In Me.PivotTables
            
                With pvt.PivotFields("SP")
                
                    .PivotItems(values(LBound(values))).Visible = True
                    
                    For Each pvtItem In .PivotItems
                    
                        pvtItem.Visible = Not IsError(Application.Match(pvtItem.Value, values, 0))
                    Next pvtItem
                End With
            Next pvt
        Next i
    wsptu_exit:
        Application.EnableEvents = True
    End Sub
    Quote Originally Posted by theBardd View Post
    I forgot to say, add a slicer on the column SP attached tio the first pivottable, and select your chosen values from there. That is what triggers the event.
    Waooo man thanks it worked like a charm. I did this workbook event thing in my Bachelors classes and did not thought to do this with workbook events. Thanks again and have a good day ahead.

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    One thing to note, if you filter PivotTable1 directly, selectin specific items in the pivottable rather than the slicer, that will update all of the others because the slicer gets updates and I check the slicer. If you filter any of the other pivottables directly, it will only affect that privottable, as the slicer doesn't then update. So you could have all pivots showing SP1, and then change pivot 2 to show SP2 as well, but only pivot 2. This may or may not be what you want, and I think it would not be hard to make all pivots reflect that change if necessary.

  6. #6
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    Quote Originally Posted by theBardd View Post
    One thing to note, if you filter PivotTable1 directly, selectin specific items in the pivottable rather than the slicer, that will update all of the others because the slicer gets updates and I check the slicer. If you filter any of the other pivottables directly, it will only affect that privottable, as the slicer doesn't then update. So you could have all pivots showing SP1, and then change pivot 2 to show SP2 as well, but only pivot 2. This may or may not be what you want, and I think it would not be hard to make all pivots reflect that change if necessary.
    Got it (Y)
    You mean to say that it will work only on PivotTable1 slicer not on any other PivotTables slicers because the event is linked to PivotTable1 slicer ONLY.
    I will not add any other slicer to limit user input so all PivotTables shows what I/he want to see.

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    No it is not the slicer but the pivottable. Any slicers, including the SP slicer, are immaterial. Slicer SP will update pivot as it attached to that pivot, and it is the update to the pivot 1 that triggers my event. In actuality, the event is triggered by any pivottable update, but as they do not update the slicer, consequently they do not update pivot 1, and the code gets the values in pivot 1/slicer.

    This should demonstrate it:
    - use the slicer to select SP1, you will see all the pivots show only SP1
    - on any pivot other than pivot 1 use the pivot filter to also select SP2, you will see SP2 on the pivot 2, but not on any of the other pivots.

    As I said, it can be amended to cater for this if required.

  8. #8
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Multiple Pivot Tables Having Different Sources

    OK Thanks for explanation
    I will look into it if required.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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