title: VBA: Method 'SetSourceData' of object '_Chart' failed For Multiple Pivot Charts
body: <p>This is part 2 of the previous question:</p>
  
  <p><a href="https://stackoverflow.com/questions/44160540/vba-runtime-error-setsourcedata-for-second-pivot-chart">VBA: Runtime Error - SetSourceData for Second Pivot Chart</a></p>
  
  <p>After following the (good) advice offered in that thread, I am still having the same problem. The title error is still showing after the second chart attempts to SetSourceData. If I add an On Error Resume Next, then the data gets wonky and unreliable. I'm starting to think it's a problem with the PivotCache, but I've attempted to differentiate the caches with different variable names. This did not solve the issue. Below is the code in it's entirety with some titles/names redacted:</p>
  
  <pre><code>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
  </code></pre>