run-time error: Methods 'add' of object 'SlicerCaches' failed in Excel2016

Yuqian

New Member
Joined
Jan 10, 2018
Messages
3
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>

 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top