Hi Catalin
Thanks for your lengthy explanation 
I do have codes that will disconnect all slicers (irrespective of data source) in a workbook and then reconnect them all again (I believe I got the codes from Contextures some time back).
If life is not easy for an expert like you, then imagine what it is like for me
Cheers
Could you please post the code to disconnect and reconnect all slicers to all pivot tables in a workbook (irrispective of prior connections)
Thanks, TheoRI
Hi Theo,
Please see post no 5 above, the code is already posted there.
Hi Catalin,
Could you share how the code can be modified just for linking all slicers in a worksheet (not the whole workbook) to all pivot tables in the same worksheet (regardless of pivot tables that may be similarly named, such as PivotTable1, in other worksheets in the same workbook)?
Hi Jose,
To connect all slicers to all pivot tables from a specific sheet, you can use this code (change the targetsheet name as needed, it's set to Sheet1 at this moment):
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
Hi Catalin,
Thank you so much for the code! I've just tested it, and it works fine in smaller sheets (5 pivot tables connected to 5 slicers).
However, I tested it on one of our standard sheets (25 slicers, 50 pivot tables) and the marco seems to run indefinitely. I will keep testing it and maybe it really just takes a while to finish running.
Again, appreciate all the help! This saves us a lot of effort.
Hi Jose,
It's not an endless loop, it's limited to the number of pivots from that sheet, but will loop though all slicercaches and its slicers from the entire workbook, there is no way that I know of to select only the slicers from a single sheet, a slicercache is stored at workbook level.
It should be a fast loop though, to read that collection, even if it's large. I suspect that you have lots of pivots and most probably lots of formulas in that workbook, when connecting a pivot the calculation might slow down your file, so you can try running the code with calculation set to manual.
Hi Catalin,
When I try your code on my worksheet I get Run-time error '1004' Application-defined or object-defined error at this line:
aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i))
I'm using Excel 365 and the data source for all pivot tables on the page is a from a Table created from a Power Query.
I've added a few slicers to one of the tables and I'm trying to connect them to the other 47 tables on the page.
If I try to connect a second table to any of the slicers manually I don't see any slicers appearing in the PivotTable Analyze > Filter Connections box.
I also don't see any of the pivot tables showing in Report Connections... list by right clicking any of the slicers. Could I have messed something up with the slicer cache by running the code as far as that line but it halting there?
If you don't see a specific pivot in Report Connections list of pivots, they have a different cache. You can try running the cache reset codes provided.
Hi, I ran that code from Debra (interestingly I couldn't find it on her website for a full explanation in context). After I did that and reopened the file, Excel told me it had to repair " Errors were detected in file '."Repaired Records: External data range from /xl/queryTables/queryTable1.xml part (External data range)".
After that it now works perfectly. Thank you
