title: Excel 2010 VBA - Update Graph using range defined by variable
body: <p>I have an Excel sheet that is updating daily.  I am trying to automatically update a graph with the new data (1 row) that is added daily.</p>
  
  <p>So far I have:</p>
  
  <pre><code>Sub UpdateGraphs()
  
      Dim latestRow As Integer
  
      Sheets("DailyJourneyProcessing").Select
  
      Range("A500").Select
  
      Do Until ActiveCell.Value = ""
          If ActiveCell.Value &lt;&gt; "" Then
              ActiveCell.Offset(1, 0).Select
          End If
      Loop
  
      ActiveCell.Offset(-1, 0).Select
      Application.CutCopyMode = False
      ActiveCell.EntireRow.Copy
  
      ActiveCell.Offset(1, 0).Select
      ActiveCell.EntireRow.PasteSpecial (xlPasteAll)
  
      Application.CutCopyMode = False
  
      latestRow = ActiveCell.row
  
      Dim str1 As String
      Dim rng1 As Range
  
      str1 = "=DailyJourneyProcessing!$F$180:$F$" &amp; latestRow
      Set rng1 = Range(str1)
  
      Debug.Print "Got this far..."
  
      Set ActiveChart.SeriesCollection(1).Values = Range(str1)
  </code></pre>
  
  <p>I know that this looks like I simply copy the previous row but the formula's included take car of the changes in data.</p>
  
  <p>The Integer / row at the moment is around 520, so I want to do:</p>
  
  <pre><code>ActiveChart.SeriesCollection(1).Values = "=DailyJourneyProcessing!$F$180:$F$520"
  </code></pre>
  
  <p>Where the row number changes daily.  This is one of about 20 range updates I need to automate, but once I have solved one the others should be the same.</p>
  
  <p>I have tried everything I can find online, but nothing quite worked.</p>
  
  <p>At the moment, I get a run-time error 91:  Object or With block variable not set.</p>
  
  <p>Any help would be appreciated.</p>