Can't Use Slicer Option - Vba Built PivTbl

[erock24]

I am building a pivot table using a vba. It works great. I've upgraded excel versions, I'm now on 2010. When my code builds the table, I'd like to add some slicers (eventually with another vba function) but that option is not available when I use the code to build the table. It is when I build it manually. What gives? I already have the code built to add the slicers, but that errors out if I try to run it after I auto-build table. Works if I manually build table then run slicer code.

Here is pivot table code:

Private Function BuildPivotTables(ByVal ws As Worksheet, ByVal pivotName As String, ByVal tableRange As Range, ByVal sourceRng As Range)
    Dim objPivotCache As PivotCache
    Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ws.Range(sourceRng.Address))
    With objPivotCache
        .CreatePivotTable TableDestination:=ws.Range(tableRange.Address), TableName:=pivotName
    End With
    With ws.PivotTables(pivotName)
        .ColumnGrand = True
        .RowGrand = True
        .SmallGrid = False
        .Format xlTable10
            With .PivotFields("Year")
                .Orientation = xlRowField
                .Position = 1
            End With
            With .PivotFields("Month")
                .Orientation = xlRowField
                .Position = 2
            End With
            With .PivotFields("New")
                .Orientation = xlDataField
                .Caption = "New "
                .Position = 1
                .Function = xlSum
                .NumberFormat = "#,##0_);[Red](#,##0)"
            End With
        ActiveWorkbook.ShowPivotTableFieldList = False
    End With
End Function

Here is Slicer Code

Private Function CreateNewSlicers(ByVal ws As Worksheet, ByVal pivotName As String)
    Dim sliceCach As SlicerCaches
    Dim sliceObj1 As Slicers, sliceObj2 As Slicers, sliceObj3 As Slicers, sliceObj4 As Slicers
    Dim slice1 As Slicer, slice2 As Slicer, slice3 As Slicer, slice4 As Slicer
    Set ws = NewLoad
    Set sliceCach = ActiveWorkbook.SlicerCaches
    Set sliceObj1 = sliceCach.Add(ws.PivotTables(pivotName), "Year", "yearSlicer").Slicers
    Set sliceObj2 = sliceCach.Add(ws.PivotTables(pivotName), "Month", "monthSlicer").Slicers
    Set sliceObj3 = sliceCach.Add(ws.PivotTables(pivotName), "Day", "daySlicer").Slicers
    Set sliceObj4 = sliceCach.Add(ws.PivotTables(pivotName), "Hour", "hourSlicer").Slicers
    Set slice1 = sliceObj1.Add(ws, , "yearSlicer", "Year", 20, 1000, 100, 50)
    Set slice2 = sliceObj2.Add(ws, , "monthSlicer", "Month", 20, 1105, 100, 50)
    Set slice3 = sliceObj3.Add(ws, , "daySlicer", "Day", 20, 1210, 100, 50)
    Set slice4 = sliceObj4.Add(ws, , "hourSlicer", "Hour", 20, 1315, 100, 50)
End Function

to recap:
Pivot code works great, except I can't use slicers either manually or with vba. Slicer code works great when I manually build pivot table, not when built with vba.

 

[crikreef]

I'm using a different code but trying to get the same result with the same problem. Did you ever find a result or can someone else post a way to work around this?

 

[crikreef]

Anyone got any ideas?

 

[crikreef]

Ttt

 

[Firefly2012]

I'm not using xl2010 at the moment I'm afraid - are slicers exposed in the VBA object model? ie can you see the properties and methods available in the VBE's object browser?