title: How to create an automated dynamic line graph in Excel VBA
body: <p>I have a work problem. I have a data report with tons of information in it and I need to create 3 line graphs to represent 3 different values over time. The time is also in the report and is the same time for all of the values. I am having trouble finding a solution specific to me in forums elsewhere. </p>
  
  <p>The data report varies in length, rows. What I need to do is to create the 3 line graphs and have them positioned horizontally, a few rows under the end of the report. Two of the graphs have one series each and the third has two series. </p>
  
  <p>This is what the graphs need to include:</p>
  
  <p>Graph 1: RPM over Time<br>
  Graph 2: Pressure over Time<br>
  Graph 3: Step burn off and Demand burn off over Time</p>
  
  <p>I am just getting into VBA because of a recent position change at work and I know very little about it but I have spent a lot of time figuring out how to write other macros for the same report. Since my verbal representation of the workbook is unclear I have attached a link to a sample of the data report for viewing.</p>
  
  <p><a href="https://dl.dropboxusercontent.com/u/28487159/Work%20Example.xls" rel="nofollow noreferrer">Data Report Workbook Download</a>
  <img src="https://i.stack.imgur.com/agCtN.gif" alt="Extract from Download + Added Charts"></p>
  
  <p>Here is what I have so far. It works for the first chart. Now what can I put in the code to name the chart "RPM" and to name the series "RPM"?</p>
  
  <pre><code>    Sub Test()
      Dim LastRow As Long
      Dim Rng1 As Range
      Dim ShName As String
      With ActiveSheet
          LastRow = .Range("B" &amp; .Rows.Count).End(xlUp).Row
          Set Rng1 = .Range("B2:B" &amp; LastRow &amp; ", E2:E" &amp; LastRow)
          ShName = .Name
  
      End With
      Charts.Add
      With ActiveChart
          .ChartType = xlLine
          .SetSourceData Source:=Rng1
          .Location Where:=xlLocationAsObject, Name:=ShName
      End With
  End Sub
  </code></pre>
  
  <p>I have figured out how to put the chart name in via VBA. The code now looks like this:</p>
  
  <pre><code>Sub Test()
      Dim LastRow As Long
      Dim Rng1 As Range
      Dim ShName As String
      With ActiveSheet
          LastRow = .Range("B" &amp; .Rows.Count).End(xlUp).Row
          Set Rng1 = .Range("B2:B" &amp; LastRow &amp; ", E2:E" &amp; LastRow)
          ShName = .Name
      End With
  
      Charts.Add
      With ActiveChart
          .ChartType = xlLine
          .HasTitle = True
          .ChartTitle.Text = "RPM"
          .SetSourceData Source:=Rng1
          .Location Where:=xlLocationAsObject, Name:=ShName
      End With
  
  End Sub
  </code></pre>
  
  <p>I will next be working on the series title and then on to having the chart place itself under the report data. Suggestions and comments welcome.</p>
  
  <p>The updated code below creates the rpm chart and the pressure chart separately. The last chart needs two series and I am working on that now. </p>
  
  <pre><code>Sub chts()
  
  'RPM chart-------------------------------------
      Dim LastRow As Long
      Dim Rng1 As Range
      Dim ShName As String
      With ActiveSheet
          LastRow = .Range("B" &amp; .Rows.Count).End(xlUp).Row
          Set Rng1 = .Range("B2:B" &amp; LastRow &amp; ", E2:E" &amp; LastRow)
          ShName = .Name
      End With
  
      Charts.Add
      With ActiveChart
          .ChartType = xlLine
          .HasTitle = True
          .ChartTitle.Text = "RPM"
          .SetSourceData Source:=Rng1
          .Location Where:=xlLocationAsObject, Name:=ShName
      End With
  
      With ActiveChart.SeriesCollection(1)
          .Name = "RPM"
      End With
  
  ' Pressure chart --------------------------------
  
      Dim LastRow2 As Long
      Dim Rng2 As Range
      Dim ShName2 As String
      With ActiveSheet
          LastRow2 = .Range("B" &amp; .Rows.Count).End(xlUp).Row
          Set Rng2 = .Range("B2:B" &amp; LastRow2 &amp; ", G2:G" &amp; LastRow2)
          ShName2 = .Name
      End With
  
      Charts.Add
      With ActiveChart
          .ChartType = xlLine
          .HasTitle = True
          .ChartTitle.Text = "Pressure/psi"
          .SetSourceData Source:=Rng2
          .Location Where:=xlLocationAsObject, Name:=ShName2
      End With
  
      With ActiveChart.SeriesCollection(1)
          .Name = "Pressure"
      End With
  End Sub
  </code></pre>
  
  <p>David, I am curious to see how your code works with my worksheet but I'm not sure how to fix the syntax error.</p>