Hi all,
So I created two pivot tables from the same source. What i want to do is to create a <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">SlicerCache</code>to connect these two tables together. The code run successfully in Excel 2010 and Excel 2013 but failed in Excel 2016. I tried SlicerCaches.Add2() but still not working. Any one can help?
Error message:
run-time error'-2147418113'(8000ffff)': Methods 'add' of object 'SlicerCaches' failed
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub OneYearTrend(wb As Workbook, wk As Worksheet)
With wb
Dim pvc As PivotCache
'Creat pivotcache
Set pvc =.PivotCaches.Create(xlDatabase, wk.Range("A1").CurrentRegion, xlPivotTableVersion14)
Dim pvt As PivotTable
'Create pivot table for new hire and separation
Set pvt = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("M6"), _
TableName:="OneYear_pvTbl_InOut")
With pvt
With.PivotFields("Type")
.Orientation = xlColumnField
.Position =1
.PivotItems("Total Workforce").Visible =False
.PivotItems("NEW HIRES").Caption ="In"
.PivotItems("SEPARATIONS").Caption ="Out"
EndWith
With.PivotFields("JobGroup")
.Orientation = xlPageField
.Position =1
.EnableMultiplePageItems =True
EndWith
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Set pvt = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("AA1"), _
TableName:="OneYear_pvTbl_Sparkline")
With pvt
With.PivotFields("ExtractDate")
.Orientation = xlRowField
.Position =1
EndWith
.AddDataField .PivotFields("White")," White", xlSum
.AddDataField .PivotFields("Black")," Black", xlSum
.AddDataField .PivotFields("Hispanic")," Hispanic", xlSum
.AddDataField .PivotFields("Asian")," Asian", xlSum
.AddDataField .PivotFields("AmericanIndian")," AmericanIndian", xlSum
.AddDataField .PivotFields("Male")," Male", xlSum
.AddDataField .PivotFields("Female")," Female", xlSum
.AddDataField .PivotFields("Total")," Total", xlSum
EndWith
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim SC As SlicerCache
Set SC =.SlicerCaches.Add(pvt,"JobGroup") 'Got error message here'
SC.Slicers.Add wk,,"JobGroup","JobGroup",50,1000,150,200
SC.PivotTables.AddPivotTable .Worksheets("Dashboard").PivotTables("OneYear_pvTbl_InOut")</code></code></code>
So I created two pivot tables from the same source. What i want to do is to create a <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">SlicerCache</code>to connect these two tables together. The code run successfully in Excel 2010 and Excel 2013 but failed in Excel 2016. I tried SlicerCaches.Add2() but still not working. Any one can help?
Error message:
run-time error'-2147418113'(8000ffff)': Methods 'add' of object 'SlicerCaches' failed
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub OneYearTrend(wb As Workbook, wk As Worksheet)
With wb
Dim pvc As PivotCache
'Creat pivotcache
Set pvc =.PivotCaches.Create(xlDatabase, wk.Range("A1").CurrentRegion, xlPivotTableVersion14)
Dim pvt As PivotTable
'Create pivot table for new hire and separation
Set pvt = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("M6"), _
TableName:="OneYear_pvTbl_InOut")
With pvt
With.PivotFields("Type")
.Orientation = xlColumnField
.Position =1
.PivotItems("Total Workforce").Visible =False
.PivotItems("NEW HIRES").Caption ="In"
.PivotItems("SEPARATIONS").Caption ="Out"
EndWith
With.PivotFields("JobGroup")
.Orientation = xlPageField
.Position =1
.EnableMultiplePageItems =True
EndWith
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Set pvt = pvc.CreatePivotTable(TableDestination:=.Worksheets("Dashboard").Range("AA1"), _
TableName:="OneYear_pvTbl_Sparkline")
With pvt
With.PivotFields("ExtractDate")
.Orientation = xlRowField
.Position =1
EndWith
.AddDataField .PivotFields("White")," White", xlSum
.AddDataField .PivotFields("Black")," Black", xlSum
.AddDataField .PivotFields("Hispanic")," Hispanic", xlSum
.AddDataField .PivotFields("Asian")," Asian", xlSum
.AddDataField .PivotFields("AmericanIndian")," AmericanIndian", xlSum
.AddDataField .PivotFields("Male")," Male", xlSum
.AddDataField .PivotFields("Female")," Female", xlSum
.AddDataField .PivotFields("Total")," Total", xlSum
EndWith
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim SC As SlicerCache
Set SC =.SlicerCaches.Add(pvt,"JobGroup") 'Got error message here'
SC.Slicers.Add wk,,"JobGroup","JobGroup",50,1000,150,200
SC.PivotTables.AddPivotTable .Worksheets("Dashboard").PivotTables("OneYear_pvTbl_InOut")</code></code></code>