title: Add data and format Pivot Chart using VBA Excel
body: <p>I am struggling to format a <code>Pivotchart</code> &amp; add data to the chart using VBA. I have setup the code to create the pivot chart which is working fine and I can manually format &amp; add data but whenever I try and mimic the macro recorder or anything I have read online it seems to fail stated 'Application-defined or object defined error.'</p>
  
  <p>I have tried to change the code to change <code>objChart</code> to <code>ActiveChart</code> trying to mimic what I done when using the macro recorder but this still fails. I tried the recorder after my initial attempts failed. </p>
  
  <p>I have also failed to have the <code>ChartTitle.Add</code> work so I can amend the title that is visible (Currently nothing shows as a title). </p>
  
  <pre><code>ActionTracker.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Actions, Version:=6).CreatePivotTable _
      TableDestination:=CtyDrng, TableName:="Country Overview", _
      DefaultVersion:=6
  
  
          Dim pt As PivotTable
          Dim PtRange As Range
          Dim ChrtRange As Range
          Dim objChart As Chart
          Dim chtSeries As SeriesCollection
  
  Set pt = CountryDash.PivotTables("Country Overview")
  
  Set PtRange = pt.TableRange1
  
  With PtRange
      Set ChrtRange = .Offset(, 1).Resize(1, .Columns.Count - 1)
      Set ChrtRange = Union(ChrtRange, .Offset(.Rows.Count - 1, 1).Resize(1, .Columns.Count - 1))
  End With
  
  Set objChart = CountryDash.Shapes.AddChart.Chart
  
  With objChart
      .SetSourceData ChrtRange
      .ChartType = xlBarStacked100
      .ShowAllFieldButtons = True
  '        .ChartTitle.Add
      '.ChartTitle.Select
  '        .ChartTitle.Text = "Country Overview"
  End With
      With objChart.PivotLayout.PivotTable.PivotFields("Country")
      .Orientation = xlRowField
      .Position = 1
  End With
  With objChart.PivotLayout.PivotTable.PivotFields("Region")
      .Orientation = xlRowField
      .Position = 2
  End With
  With objChart.PivotLayout.PivotTable.PivotFields("Overdue")
      .Orientation = xlRowField
      .Position = 3
  End With
     objChart.PivotLayout.PivotTable.AddDataField objChart.PivotLayout. _
      PivotTable.PivotFields("Overdue"), "Overdue", xlCount
  </code></pre>
  
  <p>I'm expecting the code to create a pivot chart by country then region showing the overdue actions with 3 different categories 'Implemented', 'In Progress' &amp; 'Overdue'.</p>