Excel Insert Slicer Control Greyed Out

[tewmaster]

Created a Pivot Table with VBA using Excel 2010 and now the slicer controls are greyed out; creating the same Pivot Table manually does allow use of the slicer controls. Is this a limitation of VBA created Pivot Tables?

 

[tewmaster]

I finally cracked the case of the Greyed Out Slicer control. It's been well documented that slicers only work with Excel 2010 going forward. I originally used the Pivot Cache 'Add' method to create the Pivot Table and the slicers were not available. I then used VBA Pivot Table Wizard code assuming that would use the latest format Excel 2010 format. It turns out that this is not the case, with the VBA Wizard code used the prior or pre-Excel 2010 format as well, hence pivot table slicers were not available. This seems to be an oversight on Microsoft's part. Since Excel 2010 was being used you would think it would use the latest, supported format, not the case. See the two code samples below to see the subtle difference. The Add method does use DefaultVersion:=xlPivotTableVersionCurrent so one would, logically at least, assume that Excel 2010 format was being used. The bottom line is that the version must be xlPivotTableVersion14 for slicers to work on VBA created Pivot Tables!

The 'Add' Method Does Not Work:

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSP. _
Cells(2, 2), TableName:="PivotTbl", DefaultVersion:=xlPivotTableVersionCurrent)

Must Use the 'Create' Method:

Set PTCache = ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion14)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSP.Cells(2, 2), _
TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion14)