VBA to connect all pivot tables to slicers|VBA & Macros|Excel Forum|My Online Training Hub

Hello Everybody!

https://www.myonlinetraininghu.....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