Using Excel 2010 Created a Pivot Table with VBA 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 although was not able to add slicers with code and make them work either.
I finally cracked the case of the Greyed Out Slicer control. It's been well documented that slicers only work with Excel 2010 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 it would use the latest format and Excel 2010 format. It turns out that this is not the case, with the VBA Wizard code used the prior or old format as well, hence pivot table slicers are 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 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)
Last edited by tewmaster; 03-25-2016 at 01:17 PM.
I finally cracked the case of the Greyed Out Slicer control. It's been well documented that slicers only work with Excel 2010 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 it would use the latest format and Excel 2010 format. It turns out that this is not the case, with the VBA Wizard code used the prior or old format as well, hence pivot table slicers are 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 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)
Last edited by tewmaster; 03-25-2016 at 01:19 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks