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

 

[Dude to Excel Dude]

Hi Jerry - attached is a file with an example. I tested the macro in a sheet that contains a Pivot Table and it works correctly. However, what I want to accomplish is to be able to disconnect slicer when they are at a different tab from the pivot table. Thanks. Link: https://app.box.com/s/njlbdu18b2t7qo5nncwf

 

[Jerry Sullivan]

Hi Jerry - attached is a file with an example. I tested the macro in a sheet that contains a Pivot Table and it works correctly. However, what I want to accomplish is to be able to disconnect slicer when they are at a different tab from the pivot table. Thanks. Link: https://app.box.com/s/njlbdu18b2t7qo5nncwf

Hi Dude,

In your example file, you have a PivotChart and Slicers on Sheet "Dashboard" and the PivotChart's PivotTable on Sheet "Pivot"

The code that you are trying (from Post #36) disconnects any PivotTables on the ActiveSheet from all Slicers.

I believe you are trying to run that macro when Dashboard is the ActiveSheet. The reason that doesn't work is that the PivotTable itself isn't on that Sheet- it's on Sheet "Pivot".

You could either run the macro while Sheet "Pivot" is the ActiveSheet or use the code from Post #34 to disconnect all Slicers in the workbook from all PivotTables in the workbook.

 

[Dude to Excel Dude]

Jerry: Thank you so much for your help, post #34 is what I was looking for.

 

[dkmanley]

Hi Jerry. Turns out, I want to reconnect the slicers after all. My process is, I paste the source data over the existing data because it always contains more rows and the headers (fields) don't change. The source data is a Table too "Tbl_IBdata". I run the Sub above in #36 "Sub DisconnectAllSlicerPivotsSheet()", then paste the new source data, refresh ALL, to update pivot tables ... now I want to re-connect the slicers. I have 8 tabs and multiple pivots and slicers on each ... example for PivotTable3 on Tab: New Orders vs IB, has 3 slicers with slicers cache(s): "Term_OR_Permanent_License" and "Slicer_Super_Region21" and "Slicer_Product_Center". As mentioned there are 3 more slicers on this tab and 7 more tabs with a total of 27 slicer cache. Is there a post in this string that will help or do you have code? Thanks again.
Dave

 

[Jerry Sullivan]

Hi Dave,

If your source data is an Excel Table (a ListObject in VBA), then there's no reason to disconnect and reconnect your slicers. You should be able to just paste your new data into the old table then resize the table (if it doesn't resize automatically on it's own).

 

[dkmanley]

Hi Jerry. Apologies if this is duplicate. I forgot to mention that in this case I also added columns/fields to the source data table, so I have to disconnect and reconnect slicers to udate pivot tables. Most of the time and once the report is refined it will be a simple copy paste so I won't have to disconnect/reconnect. Thanks.
Dave

 

[Jerry Sullivan]

Dave, You can add columns/fields to the source data table without the need to disconnect and reconnect slicers.

If you've already disconnected all the slicers, it should only take a minute or two to reconnect the slicers manually (even 3x7=27 connections). VBA code could be used to automate connecting all the Pivots to specified slicers; however it would take longer to setup and run than to just do it manually.

The earlier code in this thread is useful to overcome the problem of resizing a data source that uses a fixed address reference without having to disconnect/reconnect slicers.
Part of that involves saving the mapping of which Pivots are connected to which Slicers. If the connections have been removed without saving the mapping, there isn't any way to automatically restore those to their previous state.

 

[dkmanley]

Jerry, I have another question related to the slicers. The slicers and source pivot tables are on separate sheets in order to hide the pivots and have the slicers manipulate the charts. This won't allow me to clear filters using the button in the menu in Sort&Filter because the pivots are missing. I wrote a macro that clears all Slicer Cache in the workbook (below). Is there a way to limit to the slicer cache in the active sheet instead of every workbook slicer? Sub ClearSlicerFilters ()
Dim cache As SlicerCache
For Each cache In ActiveWorkbook.SlicerCaches
cache.ClearManualFilter
Next cache
End Sub

 

[Jerry Sullivan]

Hi again Dave,

If I'm understanding you correctly, then this would be one way to do that....

Sub ClearSlicersOnActiveSheet()
 '--clears all filters for any slicerCache that has slicers in
 '    the activeSheet

 Dim slc As SlicerCache
 Dim sl As Slicer
 
 For Each slc In ActiveWorkbook.SlicerCaches
   For Each sl In slc.Slicers
      If sl.Parent.Name = ActiveSheet.Name Then
         slc.ClearAllFilters
      End If
   Next sl
 Next slc
End Sub

 

[dkmanley]

Hi Jerry. Thank you. I had found a solution that worked(below). I plugged in your code and it had an error at… slc.ClearAllFilters<o></o>
<o> </o>
<o> </o>
Below is what I found that works. Do you see anythingwrong with it? <o></o>
<o> </o>
Sub ClearSlicerFilterActiveSheet()<o></o>
RefreshSlicersOnWorksheet ActiveSheet<o></o>
End Sub<o></o>
________________________________________________<o></o>
Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)<o></o>
Application.ScreenUpdating = False<o></o>
Application.Calculation = xlCalculationManual<o></o>
Dim sc AsSlicerCache<o></o>
Dim scs AsSlicerCaches<o></o>
Dim slice AsSlicer<o></o>
Set scs =ws.Parent.SlicerCaches<o></o>
If Not scs IsNothing Then<o></o>
For Each scIn scs<o></o>
ForEach slice In sc.Slicers<o></o>
Ifslice.Shape.Parent Is ws Then<o></o>
sc.ClearManualFilter<o></o>
Exit For 'unnecessary to check the other slicers of the slicer cache<o></o>
EndIf<o></o>
Nextslice<o></o>
Next sc<o></o>
End If<o></o>
Application.Calculation = xlCalculationAutomatic<o></o>
Application.ScreenUpdating = True<o></o>
<o> </o>
End Sub<o></o>