Forum

VBA to connect all ...
 
Notifications
Clear all

VBA to connect all pivot tables to slicers

3 Posts
2 Users
0 Reactions
299 Views
(@btadams)
Posts: 2
New Member
Topic starter
 

Hello Everybody!

https://www.myonlinetraininghub.com/excel-forum/vba-macros/how-to-use-macros-to-manage-report-connections-in-slicers

I'm using the code from the link above to link all pivot tables to all slicers. Here's the code:

Sub ConnectAllPivotsToAllSlicers()
Dim TargetPTDict As Variant, TargetSheet As Worksheet, PvTable As PivotTable
Set TargetPTDict = CreateObject("Scripting.Dictionary")
Set TargetSheet = ThisWorkbook.Worksheets("Sheet1")
Dim slCache As SlicerCache, slCachePivotTable As PivotTable, SlItem As Variant, pt As Variant, i As Byte, aSlicer As Slicer

'get the list of pivot tables from that sheet
For Each PvTable In TargetSheet.PivotTables
    TargetPTDict.Add Key:=PvTable.Parent.Name & PvTable.Name, Item:=PvTable
Next

'loop through all slicers from all slicercaches
For Each slCache In ThisWorkbook.SlicerCaches
    For Each aSlicer In slCache.Slicers
        If aSlicer.Parent.Name = TargetSheet.Name Then 'this slicer is in the target sheet
            For Each SlItem In TargetPTDict.Keys
                'connect all pivots to this slicer
                pt = TargetPTDict.Items
                If UBound(pt) >= LBound(pt) Then
                    For i = LBound(pt) To UBound(pt)
                        aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i))
                    Next
                End If
             Next
         End If
    Next
Next

Set TargetPTDict = Nothing

End Sub

My Excel 2010 workbook has over 100 pivot tables and two slicers. When the code hits the line below

For Each slCache In ThisWorkbook.SlicerCaches

it just skips down to Set TargetPTDict = Nothing

So it seems it is not detecting any SlicerCaches. Does anyone know why?

Thanks,

Brian

 
Posted : 09/06/2020 10:15 am
(@btadams)
Posts: 2
New Member
Topic starter
 

I figured it out. Needed to change ThisWorkbook to ActiveWorkbook as I had stored the macro in my PMW.

 
Posted : 09/06/2020 1:06 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

Good job.  Thanks for letting us know.

 
Posted : 10/06/2020 8:20 pm
Share: