Hello Everybody!
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
NextSet 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
I figured it out. Needed to change ThisWorkbook to ActiveWorkbook as I had stored the macro in my PMW.
Good job. Thanks for letting us know.