title: Creating an Excel macro for multiple graphs with the same x-values but different y-values
body: <p>I'm trying to automate the creation of graphs in Excel by means of a macro.
  Basically I've got a lot of data (41 columns, starting from column C to column AQ) each one containing another 'name'. In one column there are two series in two different row sections (one from row 3 to 8, one from row 12 to 17). </p>
  
  <p>I want one graph per column, so in total I'll have 41 graphs. Each graph contains the two series. In column B are the values on the X axis, and this is for every graph the same.</p>
  
  <p>The purpose is to create a for next loop with the y-axis values, and have them all created at once.</p>
  
  <p>I've tried coding the first part (just creating one graph with on x-axis the values from B3:B8 and on y-axis the values from C3:C8, and the second series just a few rows down.</p>
  
  <p>I get an error and I don't understand why, I've tried different approaches but it never works. The error situates in this line: ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value</p>
  
  <p>Does anyone have any ideas what may be wrong or how to tackle this problem?</p>
  
  <pre><code>Sub Macro5()
  
      ActiveSheet.Shapes.AddChart.Select
      ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
      ActiveChart.SeriesCollection.NewSeries
      ActiveChart.SeriesCollection(1).Name = "='Blad1'!$A$1"
      ActiveChart.SeriesCollection(1).XValues = "='Blad1'!$B$3:$B$8"
      ActiveChart.SeriesCollection(1).Values = Range(Cells(3, 3), Cells(8, 3)).Value
      ActiveChart.SeriesCollection.NewSeries
      ActiveChart.SeriesCollection(2).Name = "='Blad1'!$A$10"
      ActiveChart.SeriesCollection(2).XValues = "='Blad1'!$B$12:$B$17"
      ActiveChart.SeriesCollection(2).Values = Range(Cells(12, 3), Cells(17, 3)).Value
      ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
      ActiveChart.Axes(xlValue).MajorGridlines.Select
      Selection.Delete
      ActiveChart.SeriesCollection(1).Trendlines.Add
      ActiveChart.SeriesCollection(1).Trendlines(1).Select
      Selection.DisplayEquation = True
      Selection.DisplayRSquared = True    
      ActiveChart.SeriesCollection(2).Trendlines.Add
      ActiveChart.SeriesCollection(2).Trendlines(1).Select
      Selection.DisplayEquation = True
      Selection.DisplayRSquared = True
  
  End Sub
  </code></pre>