Filter Multiple Pivot Tables Having Different Sources

[fiberboysa]

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.

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...!

 

[theBardd]

Try this worksheet event procedure

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

 

[theBardd]

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.

 

[fiberboysa]

Try this worksheet event procedure

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

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.

 

[theBardd]

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.

 

[fiberboysa]

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.

 

[theBardd]

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.

 

[fiberboysa]

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