By the way, this recorded macro works, but I don't want to use it. I'm trying not to do this much hardcoding of values. That's why my procedure is trying to be modestly clever about this. Also, if I run this twice, I get an error that the slicer cache already exists.
wb.SlicerCaches.Add2( _
ws.PivotTables("PT_Velocity_Forecast"), _
"[Table_Velocity].[ART]").Slicers.Add _
ws, _
"[Table_Velocity].[ART].[ART]", _
"ART", _
"ART", _
169.5, 816, 144, 155.5
The method seems quite temperamental about its argument types. I'd suggest:
Dim pf As PivotField
For Each pf In ptObject.RowFields
' Create slicer cache and slicer for selected PT fields
Select Case pf.Caption
Case "ART", "TEAM", "PI", "SPRINT", "VALID", "WARNING"
strSourceField = pf.CubeField.Name
strSlcrName = pf.Caption
strSlcrCaption = pf.Caption
Set rng = Range("Q6:T6")
lSpace = lSpace + 160
' The empty param is the OLAP hierarchy level, which is N/A
Set slcr = wb.SlicerCaches.Add2( _
Source:=CVar(ptObject), _
SourceField:=CVar(strSourceField)).Slicers.Add( _
SlicerDestination:=ws.Name, _
Name:=ws.Name & " " & pf.Caption, _
Caption:=pf.Caption, _
Top:=rng.Top, Left:=50, Width:=150, Height:=120)
'With slcr
' .NumberOfColumns = 3
' .RowHeight = 13
' .ColumnWidth = 70
'End With
Case Else
' do nothing
End Select
Next pf
End Sub
and for your reset code it looks like you might as well just loop through all the slicercaches and delete them (that should delete the associated slicers at the same time).
