VBA: Il metodo 'SetSourceData' dell'object '_Chart' non è riuscito per i grafici multipli di perno

Questa è la parte 2 della precedente domanda:

VBA: Errore di runtime – SetSourceData per il secondo grafico pivot

Dopo aver seguito i consigli (buoni) offerti in quel thread, ho ancora lo stesso problema. L'errore del titolo si sta ancora mostrando dopo che il secondo grafico tenta di impostare SetSourceData. Se aggiungo un Resume di errore su Avanti, i dati diventano inafferrabili e inaffidabili. Sto cominciando a pensare che sia un problema con la PivotCache, ma ho cercato di differenziare le cache con nomi di variables differenti. Questo non ha risolto il problema. Di seguito è riportto il codice nella sua interezza con alcuni titoli / nomi modificati:

Option Explicit Sub CreatePivots() Call CreateBarPivot Call CreatePiePivot End Sub Sub CreateBarPivot() Dim myWB As Workbook Dim PSheet, DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Dim ChartWidth As Range Dim BPivot As Shape 'Define Workbook Set myWB = ThisWorkbook 'Define worksheets Set PSheet = myWB.Sheets("Tools") Set DSheet = myWB.Sheets("Aggregate") 'Define last data points LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Selects first to last filled row, and first to last filled column for data Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Create pivot cache Set PCache = myWB.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange) 'Create pivot table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Range("A1"), _ TableName:="ExcPT") 'Create pivot chart Set BPivot = PSheet.Shapes.AddChart BPivot.Chart.SetSourceData Source:=Range("$A$1:$C$18"), PlotBy:=xlRows BPivot.Chart.ChartType = xlColumnStacked ActiveWorkbook.ShowPivotTableFieldList = False With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted") .Orientation = xlPageField .Position = 1 End With With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted") .Orientation = xlColumnField .Position = 1 End With With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted") .Orientation = xlRowField .Position = 1 End With With BPivot.Chart.PivotLayout.PivotTable.PivotFields("redacted") .Orientation = xlRowField .Position = 2 End With 'Insert Data With PSheet.PivotTables("ExcPT").PivotFields("Exception") .Orientation = xlDataField .Position = 1 .Caption = "Exception Status Count" .Function = xlCount End With 'Hide Not Due With BPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception Status") .PivotItems("Not due").Visible = False End With 'Move bar chart to Dashboard; resize Set ChartWidth = Sheets("Dashboard").Range("B2:L25") With BPivot.Chart.Parent .Height = ChartWidth.Height .Width = ChartWidth.Width .Top = ChartWidth.Top .Left = ChartWidth.Left End With BPivot.Chart.ChartArea.Select BPivot.Chart.Location Where:=xlLocationAsObject, Name:="Dashboard" End Sub Sub CreatePiePivot() Dim myWB As Workbook Dim PSheet, DSheet As Worksheet Dim PCache1 As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long Dim ChartWidth As Range Dim PPivot As Shape 'Define Workbook Set myWB = ThisWorkbook 'Define worksheets Set PSheet = myWB.Sheets("Tools") Set DSheet = myWB.Sheets("Aggregate") 'Define last data points LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Selects first to last filled row, and first to last filled column for data Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Create pivot cache Set PCache1 = myWB.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange) 'Create pivot table Set PTable = PCache1.CreatePivotTable _ (TableDestination:=PSheet.Range("F1"), _ TableName:="ExcPT1") 'Create pivot chart Set PPivot = PSheet.Shapes.AddChart PPivot.Chart.SetSourceData Source:=Range("$F$1:$H$18"), PlotBy:=xlRows PPivot.Chart.ChartType = xlPie ActiveWorkbook.ShowPivotTableFieldList = False 'Insert row With PPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception") .Orientation = xlRowField .Position = 1 End With 'Insert Data With PSheet.PivotTables("ExcPT1").PivotFields("Exception") .Orientation = xlDataField .Position = 1 .Caption = "Exception Status Count" .Function = xlCount End With 'Hide Not Due With PPivot.Chart.PivotLayout.PivotTable.PivotFields("Exception Status") .PivotItems("Not due").Visible = False End With 'Move pie chart to Dashboard; resize Set ChartWidth = Sheets("Dashboard").Range("B26:L49") With PPivot.Chart.Parent .Height = ChartWidth.Height .Width = ChartWidth.Width .Top = ChartWidth.Top .Left = ChartWidth.Left End With PPivot.Chart.ChartArea.Select PPivot.Chart.Location Where:=xlLocationAsObject, Name:="Dashboard" With ActiveChart .HasTitle = False End With End Sub