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

 

[Jerry Sullivan]

Dave,

The SlicerCache.ClearAllFilters method was added in Excel 2013. So if you have Excel 2010 you would need to use .ClearManualFilter instead.

I'm not sure there's a functional difference. When I chose to use .ClearAllFilters, I assumed that would be better because it would also clear any filters that were applied using criteria based filters like CustomerName "Begins with" "B" instead of manual checkboxes or slicers.

In testing, .ClearManualFilter appears to clear criteria based filters as well.

The other code example you found looks fine- they are similar in approach with a few variations.
Exiting the For loop when a match is found on a worksheet is good idea that I hadn't considered.

 

[Heureka]

Hi, your piece of code to refresh the source of PivotTables with connected slicers is excellent!
I did build it into my Dashboard solution with a little modification: instead of setting fixed slicer names in the code I'm running a loop through all Slicer Caches and assign the names automatically (in my peticular case I have 29 slicers):

Dim vSlicerList(1 To 29) As Variant
Dim oSlicercache As SlicerCache

'--fill list of slicers.
    For Each oSlicercache In ActiveWorkbook.SlicerCaches
      vSlicerList(oSlicercache.Index) = oSlicercache.Name
    Next

Now I did run into troubles: I promise, last week, the code WAS working fine, but this week I get the error message:
"1004: Application defined or object-defined error".

A trace revealed, that the error happens on the following line of the original code, which is within the loop to store the current slicer connections:

            '--Disconnect from Slicer
            .PivotTables.RemovePivotTable (PT)

I did setup a separate workbook with a dummy pivot table and connected slicers. There again, the code is working :-O
Don't know, where to look next and hope you can give some directions for further investigations.
Thanks in advance,
Frank

 

[alemarquezr]

Hi Jerry,

I followed the whole post and I found some good tips for my problem, however my situation is slightly different.

I need to disconnect my slicers, pick some choices from the slicers, and then reconnect the slicers back to the same pivots but then by reconnecting I refresh my pivots based on the options (choices) I selected when slicers were OFF.

Can you help me to do that? I have two separate buttons for each action OFF and ON.

Thanks in advance.

AM

 

[David Wayne]

Jerry, forgive my ignorance but this code almost works for me. I have 5 slicers connected to my pivot tables and am trying to assign a macro button to disconnect all slicers from pivots. That said, I have some questions about your code:

What does the "Dim i As Long" statement mean?
Do I have to assign another letter other than "i" to the other slicers?
What does "For i = .Count To 1 Step -1 .RemovePivotTable (.Item(i)) Next i" mean?