title: Excel VBA Script to dynamically add series to chart
body: <p>I'm trying to dynamically add multiple series to a line chart. I don't know beforehand how many series there are so it needs to be dynamic. What I've come up with but doesn't work is the following:</p>
  
  <p>The sheet ActiveSheet (or Sheets("Data")) has Rows from C14 until Cend containing the XValues and Columns from E14:Eend until R14:Rend where "end" marks the last row of data as determined by column C. The series names are stored in row 9. XValues are the same for all series.</p>
  
  <p>My big problem is, that I can't find a way to dynamically add all the data columns as series to my chart together with the respective name. I'm not an expert in VBA so please be kind. I already read various sources and tried many scripts, none seem to work. The Object Catalogue was a bit of a help, however my problem persists.</p>
  
  <pre><code>Sub MakeChart()
  Dim LastColumn As Long
  Dim LastRow As Long
  Dim i As Integer
  Dim u As Integer
  Dim NameRng As String
  Dim CountsRng As Range
  Dim xRng As Range
  
      LastColumn = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
      ColumnCount = LastColumn - 4
      LastRow = ActiveSheet.Range("C" &amp; ActiveSheet.Rows.Count).End(xlUp).Row
  '   Debug.Print ("Last Column: " &amp; LastColumn &amp; " Count: " &amp; ColumnCount &amp; " LastRow: " &amp; LastRow)
  
      Charts.Add
      With ActiveChart
          .ChartType = xlLineMarkers
          .HasTitle = True
          .ChartTitle.Text = "Test"
      End With
  
      For i = 1 To ColumnCount
          u = i + 4
         NameRng = Sheets("Data").Range("R9:C" &amp; u).Value
         Set xRng = Sheets("Data").Range("R14:C3", "R" &amp; LastRow &amp; ":C3")
         Set CountsRng = Sheets("Data").Range("R14:C" &amp; u, "R" &amp; LastRow &amp; ":C" &amp; u)
  '      Debug.Print ("CountsRng: R14:C" &amp; u &amp; ", R" &amp; LastRow &amp; ":C" &amp; u &amp; " NameRng: " &amp; NameRng &amp; " xRng: R14:C3 , R" &amp; LastRow &amp; ":C3")
              ActiveChart.SeriesCollection.NewSeries
              ActiveChart.SeriesCollection(i).XValues = xRng
              ActiveChart.SeriesCollection(i).Values = CountsRng
              ActiveChart.SeriesCollection(i).Name = NameRng
      Next i
  
  End Sub
  </code></pre>