how to determine SlicerCaches?

[storm8]

Hello,
I have a workbook with data and many sheets which contain various pivot tables for that data. After I create new pivot table, I want to add slicers to it. So far I was able to create the slicers the way I want:

ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(c_pt), sname).Slicers.Add ActiveSheet, , sxname, sname, pv, ph, sw, sh

(sname is name of the field and slicer, sxname is the name and random number (because in the workbookt there are many slicers with the same name eg month), the rest is position...)
however I would also like to modify some of its properties, such as number of columns, row height, etc

ActiveWorkbook.SlicerCaches("Slicer_month5").Slicers("month 5").NumberOfColumns = 2

For this I need to know the slicerCaches value, which I do not control when creating.

How do I do this? Also how do I connect the slicer to all pivottables on the worksheet?

thank you very much!

 

[Andrew Poulsom]

Assign what's returned by the SlicerCaches Add method to an object variable and use that variable thereafter.

 

[storm8]

Thank you Andrew, but can you please tell me how to do it?

 

[Andrew Poulsom]

Something like this (I can't test because I don't have Excel 2010 here):

    Dim SC As SlicerCache
    Set SC = ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(c_pt), sname)
    With SC
        .Slicers.Add ActiveSheet, , sxname, sname, PV, ph, sw, sh
        .Slicers("month 5").NumberOfColumns = 2
    End With

 

[storm8]

works! Many thanks Andrew

 

[JonesyUK]

Hi Andrew, please can you assist. The following code RESETS ALL Slicers in the entire workbook...

How do I get this to run on the ActiveSheet Only please???

Sub Slicer()
Dim slcr As SlicerCache
For Each slcr In ActiveWorkbook.SlicerCaches
slcr.ClearManualFilter
Next slcr
Call Refresh
End Sub

Many thanks,
Clint

 

[Andrew Poulsom]

Please stick with the new thread you created:

http://www.mrexcel.com/forum/excel-questions/663869-refresh-all-pivot-slicers-activesheet-only.html