Assign different SlicerCache to a Slicer in Excel PivotTable

Question

I have two Pivot Tables with the same PivotCache and similar set of Slicers. Some of the Slicers control both Pivot Tables, which means that for example for PivotField [Month] there are total 2 Slicers which share the same SlicerCache.

Now I need to 'unsynchronize' those slicers so that each controls its own Pivot Table. I can't right-click on a slicer and do it via "PivotTable Connections", because the change made in one slicer will be reflected in another.

So I created a new SlicerCache:

ThisWorkbook.SlicerCaches.Add ws.PivotTables(2), "[Month]"

And want to assig this new SlicerCache to one of the slicers, but both methods fail:

   oSlicer.SlicerCache = ThisWorkbook.SlicerCaches(38) 'Object doesn't support this property or method
   oSlicer.SlicerCache.Index = 38 'invalid property assignment

Any Ideas? I know that I can achieve this by deleting and recreating one of the slicers, but it must be done programatically.

Answer 1

I don't believe it's possible. Why not just delete the slicers programmatically and add them again, with the connections as you require? Or if you need to toggle a slicer between having multiple connections and just one, you could have two slicers stacked on top of each other, and just bring the one you want to the top.

I wrote an article some time back about SlicerCaches that you might find useful at http://dailydoseofexcel.com/archives/2014/08/05/slicers-and-slicercaches/