Unable to reconnect slicers after disconnecting and changing data sources

rongheng

New Member
Joined
Feb 6, 2020
Messages
1
Office Version
2016
Platform
Windows
Hi,

I have many pivot tables & slicers on different sheets of my excel workbook. The file is constantly being updated(new/changing tables) by many people so my script has to update the data and all the data sources in all the pivot tables.
The objective is to:
1. Dynamically identify the slicers connected to the pivot tables
2. Disconnect the slicers from the pivot tables
3. Update all the data sources
4. Refresh all the pivot tables
5. Reconnect the slicers to the pivot tables like it originally was identified in step 1

I found the code to disconnect and reconnect the slicers but I am encountering the error "Run-time error '1004' Application-defined or object-defined error" when trying to reconnect the slicers.
VBA Code:
'Disconnect all pivot table slicers
Dim SlicersDict As Variant
Dim PTDict As Variant
Set SlicersDict = CreateObject("Scripting.Dictionary")
Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant, pvtbl As Variant, i As Byte
'create a dictionary of dictionaries with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
    Set PTDict = CreateObject("Scripting.Dictionary")
    For Each slpt In sl.PivotTables
        PTDict.Add Key:=slpt.Parent.Name & slpt.Name, Item:=slpt
    Next
    SlicersDict.Add Key:=sl.Name, Item:=PTDict
Next
 
For Each SlItem In SlicersDict.Keys
   'remove pvtbl connections for this slicer
    Set PTDict = SlicersDict(SlItem)
    pvtbl = PTDict.items
    If UBound(pvtbl) >= LBound(pvtbl) Then
        For i = LBound(pvtbl) To UBound(pvtbl)
            pvtbl(i).SaveData = True
            ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (pvtbl(i))
        Next
    End If
Next

'Update all pivot table data source
For Each wsPT In wb1.Sheets
    If wsPT.PivotTables.Count > 0 Then
        For Each pvtbl In wsPT.PivotTables
             pvtbl.ChangePivotCache wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="data_range")
        Next
    End If
Next

'Refresh all pivot tables
ActiveWorkbook.RefreshAll

'Reconnect all pivot table slicers
For Each SlItem In SlicersDict.Keys
    Set PTDict = SlicersDict(SlItem)
    pvtbl = PTDict.items

    'reconnect all pivot tables to this slicer
    If UBound(pvtbl) >= 0 Then
        For i = LBound(pvtbl) To UBound(pvtbl)
            ThisWorkbook.SlicerCaches(SlItem).PivotTables.AddPivotTable (pvtbl(i))
        Next
    End If
Next
Set SlicersDict = Nothing
Set PTDict = Nothing
The error is on this line:
VBA Code:
ThisWorkbook.SlicerCaches(SlItem).PivotTables.AddPivotTable (pvtbl(i))
I did much reading and I think it has something to do with attaching slicers created from one Pivot Cache to Pivot Tables associated with another not being possible.
However, I still cannot figure it out. Would appreciate if anyone can help me out.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,739
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top