VBA disconnect/connect slicers in active worksheet

[Vidar]

Hi
Sometimes I need to add an extra column to my pivot table by changing the Data Source.
But Excel prompts me to first disconnect my slicers since I have two or more pivottables
on the same worksheet sharing the same pivotcache.

Tried to use the macro recorder to figure out a way to loop through each slicer
and disconnect every pivottable from the slicer.
I'm not familiar with the pivottable/slicer VBA objects.

Vidar

 

[Vidar]

"Bump..."?

 

[Jerry Sullivan]

Hi Vidar,

You could use this code to disconnect all pivot tables currently connected to a slicer cache.
Please clarify if you wanted something else like stepping through more than one slicer cache, or only disconnecting pivots that are on one sheet, but retaining the connection with pivots on other sheets.

Sub Disconnect_Slicer_Pivots()
    Dim PT As PivotTable
    Dim i As Long
    
    With ActiveWorkbook.SlicerCaches("Slicer_City").PivotTables
        For i = .Count To 1 Step -1
            .RemovePivotTable (.Item(i))
        Next i
    End With
End Sub

 

[Vidar]

Hi Jerry.
By adjusting your code I was able to disconnect all slicers from all pivottables in the workbook.
Thank you for the help

Vidar

 

[rfalch]

Hi Vidar,

You could use this code to disconnect all pivot tables currently connected to a slicer cache.
Please clarify if you wanted something else like stepping through more than one slicer cache, or only disconnecting pivots that are on one sheet, but retaining the connection with pivots on other sheets.

Sub Disconnect_Slicer_Pivots()
    Dim PT As PivotTable
    Dim i As Long
    
    With ActiveWorkbook.SlicerCaches("Slicer_City").PivotTables
        For i = .Count To 1 Step -1
            .RemovePivotTable (.Item(i))
        Next i
    End With
End Sub

How do we then automate the reconnection of the pivot tables? I have a sheet and modified the above code with a "For Each" loop and then removed the connection in each Slicer. However, I now want to automate the reconnection process. However, .addPivotTable (.Item(i)) won't work because there are no longer any pivot tables connected in the slicer.

Looking forward to you your thoughts...

 

[Jerry Sullivan]

There could be a better way, but one approach would be to store the connected Pivots in an Array then remap each Pivot's sourcedata range and reconnect it to the slicer.

Sub Change_SourceData_Of_Slicer_Connected_Pivots()
    Dim vPivots As Variant
    Dim i As Long

    
    With ActiveWorkbook.SlicerCaches("Slicer_City").PivotTables
        If .Count = 0 Then Exit Sub
        ReDim vPivots(1 To .Count)

            
        '--store reference to each Pivot then disconnect from slicer
        For i = .Count To 1 Step -1
            Set vPivots(i) = .Item(i)
            .RemovePivotTable (.Item(i))
        Next i

        '--change PivotCache of Pivots and reconnect to Slicer
        For i = 1 To UBound(vPivots)
            If i = 1 Then
                vPivots(1).ChangePivotCache _
                    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                    SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion)
            Else
                vPivots(i).CacheIndex = vPivots(1).CacheIndex
            End If
            .AddPivotTable vPivots(i)
        Next i

        
    End With
End Sub

 

[DutchArjo]

I'm very happy I found this thread. I am dealing with the exact samen question and disconnection the slicers (and after that, reconnecting them again) was so far the only problem left. wat doet the string "slicer_city" mean? I'm asking that to find out if I can just simply copy this code with adjusting a few things to disconnect all slicers in mij work book and after that, reconnect them to the right pivots again. many thanks already!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

In that example Slicer_City is the name of the SlicerCache. If the user had a PivotField named "City" the default name for a SlicerCache would be Slicer_City.

To find the name of your SlicerCache, Right-Click the Slicer > Slicer settings... >
in the Slicer Settings window, the SlicerCache name is next to "Name to use in formulas: "

 

[DutchArjo]

many many thanks! I'll start testing and modifying asap! (I have 4 different slicers, therefore I have to execute the code 4 times, each time for a different slicer I guess?

 

[DutchArjo]

I've copied it now for a total of 4 slicers to disconnect, change sourcedata, then reconnect. But the problem is that it disconnects one slicers and then changes sourcedata when 3 other slicers are still connected so changing sourcedate still won't be succesfull. How can I adjust the code in a way that for all slicers the connected pivots are stored, alle slicers are disconnected, the sourcedata for all pivots is changed and the alle pivots are connected again to the proper slicers?(is this is possible)