How do I make the data update in a graph in a VBA macro?

Question

So I figured out how to select the bottom three rows of the spreadsheet:

Range("A" & Cells.Rows.Count).End(xlUp).Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(-2, 6)).Select

However, I can't figure out how to relate my selection to the data range selected in my recorded macro:

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$26:$G$28")

Any suggestions you have would be much appreciated.

I'm trying to make a macro that graphs the last three rows in a sheet as data is added (through another macro) over time. Thanks! — Will French, Jun 12 '15 at 20:17
http://stackoverflow.com/questions/19985930/excel-2010-vba-activechart-setsourcedata-failed — Siddharth Rout, Jun 12 '15 at 20:25
http://stackoverflow.com/questions/14388270/setting-a-chart-source-data-based-on-a-variable-cell-range-in-vba — Siddharth Rout, Jun 12 '15 at 20:33
http://stackoverflow.com/questions/9703612/excel-vba-charting-editting-the-range — Siddharth Rout, Jun 12 '15 at 20:33
http://stackoverflow.com/questions/10792732/excel-variable-ranges-while-creating-a-chart — Siddharth Rout, Jun 12 '15 at 20:33

score 0 · Answer 1 · edited May 23 '17 at 11:51

You have to fully qualify your Ranges. This issue shows up once and again (e.g., this).

What does this mean? Do not use Cells, Range, Rows or Columns without specifying which Worksheet they belong to, unless you specifically want to do that (and even in that case, explicitly using ActiveSheet improves readability and reduces the chances of errors, similar to using Option Explicit). For instance, replace

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$26:$G$28")

with

Dim wsdata as Worksheet
Set wsdata = ActiveWorkbook.Sheets("Sheet1")
ActiveChart.SetSourceData Source:=wsdata.Range("$A$26:$G$28")

*Note *: You can work with the ActiveCell only when the Worksheet that it is on is the ActiveSheet [MSDN].

How do I make the data update in a graph in a VBA macro?

1 Answers1