Hello,

I have run into the problem of not being able to programmatically do the following: a) disconnect pivot table slicers; b) change pivot table source (different workbooks); c) reconnect slicers.

I need this because depending on what users want to see, different pivot table data needs to be loaded but I can't change the pivot table source without disconnecting the slicers first.

All pivot tables in this workbook have the same pivot cache.
There are 4 slicers ("Slicer_Office", "Slicer_Week", "Slicer_Name", "Slicer_Name1"), 2 of which connect to tall pivot tables in the wb, and 2 of which connect to different tables.

I've looked elsewhere in the forum and have found something that looks like it should work but I only get errors when I try to run it.

Here is the most promising code I have found so far: Excel forum thread

Here is how Izandol's code looks when I try to adapt it for my use (but I get an error):
Sub test3()


 Dim PT                          As PivotTable
   Dim ptMain                      As PivotTable
   Dim ws                          As Worksheet
   Dim oDic                        As Object
   Dim oPivots                     As Object
   Dim i                           As Long
   Dim lIndex                      As Long
   Dim Max                         As Long
   Dim vPivots
   Dim vSlicers
   Dim vItem
   
 Dim sNewSource As String
   sNewSource = "C:\spreadsheets\test10day.xlsm"
 
   vSlicers = Array("Slicer_Office", "Slicer_Week", "Slicer_Name", "Slicer_Name1")
   Set oDic = CreateObject("Scripting.Dictionary")


   ' disconnect slicers
   For Each vItem In vSlicers
      With ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables
         If .Count > 0 Then
            Set oPivots = CreateObject("Scripting.Dictionary")
            For i = .Count To 1 Step -1
               oPivots.Add .Item(i).Name, .Item(i)
               .RemovePivotTable .Item(i)
            Next i
            oDic.Add vItem, oPivots
         End If
      End With
   Next vItem

   ' update pivottables
   For Each ws In ActiveWorkbook.Worksheets
      For Each PT In ws.PivotTables
         If lIndex = 0 Then
            PT.ChangePivotCache _
                  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                    SourceData:=sNewSource)
            Set ptMain = PT
            lIndex = 1
         Else
            PT.CacheIndex = ptMain.CacheIndex
         End If
      Next PT
   Next ws

   ' reconnect slicers
   For Each vItem In vSlicers
      If oDic.Exists(vItem) Then
         Set oPivots = oDic(vItem)
         vPivots = oPivots.Items
         For i = LBound(vPivots) To UBound(vPivots)
            ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables.AddPivotTable vPivots(i)
         Next i
      End If
   Next vItem

   Set oDic = Nothing
   
   End Sub
So far I get an "Run-time error '5': Invalid procedure call or argument" error at line:
 With ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables
Please help me,

Geoff.