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

 

[dkmanley]

Hi Jerry, Thanks for the quick response. Actually I was unaware that when I add an addition column to the source data Table for a formula (which I do all the time as as I build a report), and try to refresh the pivot and slicers, the slicers apparently restrict the pivot table from udating with the new field. So I guess I need to disconnect then re-connect after all. Thanks again. Dave

 

[Jerry Sullivan]

Dave, As noted in Post #11 above, the much simpler approach is to use a Named Range Reference (such as a Table, Dynamic Named Range or Static Named Range) as the PivotTable Data Source.

The code that I suggested in Post #19 can be used to convert existing PivotTables to use a Named Range Reference. I wouldn't suggest using that code every time you want to change your data source.

 

[dkmanley]

Hi Jerry, I was looking through the posts to see if you already answered this question but did not see it. Is there a way to disconnect multiple slicers in an active sheet or workbook for that matter WITHOUT having to code each individual slicer cache? I can reconnect manually. I find I have multiple slicer cache for various sheets, ex, Slicer_Super_Region21 and Slicer_Super_Region. I also have 50 possible data fields I can add a slicer for, so as I develop the workbook, it would be good to just disconnect all slicers with one click and not have to update code. Thanks. Dave

 

[Jerry Sullivan]

Hi Dave,

This code should do that...

Sub DisconnectAllSlicerPivots()
'--disconnects all pivots from all slicerCaches
'    in activeworkbook
'
 Dim pvt As PivotTable
 Dim slc As SlicerCache
    
 For Each slc In ActiveWorkbook.SlicerCaches
   For Each pvt In slc.PivotTables
      slc.PivotTables.RemovePivotTable pvt
   Next pvt
 Next slc
End Sub

 

[dkmanley]

Thank you Jerry that works great for the entire workbook. I tried to modify for Active Sheet but can't get that to work. Here is what I used but breaks on the modified line (underlined)
Sub DisconnectAllSlicerPivotsSheet()
'--disconnects all pivots from all slicerCaches
' in activesheet'
Dim pvt As PivotTable
Dim slc As SlicerCache

For Each slc In ActiveWorkbook.ActiveSheet.SlicerCaches
For Each pvt In slc.PivotTables
slc.PivotTables.RemovePivotTable pvt
Next pvt
Next slc
End Sub

Thanks Dave

 

[Jerry Sullivan]

Dave, I missed your earlier request regarding just handing pivots on the ActiveSheet.

Here's a Sub to do that....

Sub DisconnectAllSlicerPivotsSheet()
 '--disconnects all pivots in activesheet from all slicerCaches
 ' 
 Dim pvt As PivotTable
 Dim slc As SlicerCache
 
 For Each slc In ActiveWorkbook.SlicerCaches
   For Each pvt In slc.PivotTables
      If pvt.Parent.Name = ActiveSheet.Name Then
         slc.PivotTables.RemovePivotTable pvt
      End If
   Next pvt
 Next slc
End Sub

 

[Dude 2 Excel Dude]

Hi Jerry, this thread is what I'm looking for. Thanks for your support. I was trying to use the macro at post #39 to disconnect all slicers from my Active Worksheet. However, for some reason it is not disconnecting them. Any ideas?

 

[Jerry Sullivan]

Hi Dude, Were you trying the code in Post #36 or another post? Post #39 hasn't been written yet, unless you're Dude from the Future!

What happens when you try to run the macro?

 

[Dude to Excel Dude]

My bad Jerry - when I run the macro nothing happens, it just runs but no changes are done.

 

[Jerry Sullivan]

Can you provide an example workbook with any sensitive data removed/changed?

You could upload it to a hosting site like box.com and post a link, or send me a PM and we'll exchange email addresses.