title: Get chart name with VBA
body: <p>I am setting up a macro to generate a chart. I have recorded a macro while I was generating the sample chart, but now I need to have the macro working independently from the name of the chart (<code>Chart 9</code> in this case) </p>
  
  <pre><code>Sheets("statistics").Select
  Sheets("statistics").Range("A101:C106").Select
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.ChartType = xlColumnStacked
  ActiveChart.SetSourceData Source:=Range("statistics!$A$101:$C$106")
  ActiveChart.ChartArea.Select
  ActiveSheet.Shapes("Chart 9").Name = "waterfall"
  ActiveChart.Location Where:=xlLocationAsObject, Name:="summary"
  ActiveSheet.ChartObjects("waterfall").Activate
  ActiveSheet.Shapes("waterfall").IncrementLeft 80
  ActiveSheet.Shapes("waterfall").IncrementTop -2200
  ActiveSheet.ChartObjects("waterfall").Activate
  ActiveSheet.Shapes("waterfall").ScaleWidth 1.6025463692, msoFalse, msoScaleFromTopLeft
  ActiveSheet.Shapes("waterfall").ScaleHeight 1.6084106153, msoFalse, msoScaleFromTopLeft
  ActiveSheet.ChartObjects("waterfall").Activate
  ActiveChart.Legend.Select
  Selection.Delete
  ActiveSheet.ChartObjects("waterfall").Activate
  ActiveChart.SeriesCollection(1).Select
  Selection.Format.Fill.Visible = msoFalse
  ActiveChart.SeriesCollection(2).Select
  ActiveChart.SeriesCollection(2).Points(6).Select
  With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent3
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0
      .Solid
  End With
  ActiveChart.SeriesCollection(2).Points(1).Select
  With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent3
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0
      .Solid
  End With
  With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0
      .Transparency = 0
      .Solid
  End With
  ActiveChart.SeriesCollection(2).Points(5).Select
  With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0
      .Solid
  End With
  With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 0, 0)
      .Transparency = 0
      .Solid
  End With
  ActiveChart.SetElement (msoElementDataLabelCenter)
  ActiveChart.SeriesCollection(2).Points(1).Select
  ActiveChart.ChartArea.Select
  ActiveChart.ChartArea.Select
  ActiveChart.SeriesCollection(2).Points(1).Select
  ActiveChart.PlotArea.Select
  ActiveChart.SeriesCollection(2).Select
  ActiveChart.SetElement (msoElementDataLabelCenter)
  ActiveChart.SetElement (msoElementPrimaryValueAxisTitleHorizontal)
  Selection.Caption = "hrs"
  ActiveChart.Axes(xlValue).AxisTitle.Select
  Selection.Left = 7
  Selection.Top = 13.028
  </code></pre>
  
  <p>I have tried</p>
  
  <pre><code>Sheets("statistics").Range("A101:C106").Select
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.ChartType = xlColumnStacked
  ActiveChart.SetSourceData Source:=Range("statistics!$A$101:$C$106")
  ActiveChart.ChartArea.Select
  Set ThisChart = ActiveChart
  ActiveSheet.Shapes(ThisChart).Name = "waterfall"
  </code></pre>
  
  <p>but it is not working</p>