Here's a thought to start you off - how about defining a "floating" range which would be re-defined each time the macro is run as long as the range always starts in the same spot?
For example, my range contains the following information:
Cell A3 = "Month"
Cell A4 to A7 = January to April/06
Cell B3 = "Value"
Cell B4 to B7 = 14 , 13, 18, 12
Cell C1 = "Rows"
Cell C2 = "Columns"
Cell D1 and D2 - see formulas in step 1 below
1. Create 2 cells which count the number of rows and columns in the range.
In cell D1, enter the formula =COUNTA(A4:A65536)
In cell D2, enter the formula =COUNTA(4:4)
Name the range D1 as "Rows" and D2 as "Cols"
Note: If you always have the same number of columns, you don't need to define a range or formula in D2...see the offset formula below.
2. Set up your starting point reference
In the above case, the range will always start right below the heading "Month", so I'll use that as my standard reference. I will name range A3 as "Start"
3. Set up a floating range
Using Insert / Name / Define, set up the following range characteristic
Names in workbook: Float
Refers to: =Offset(Start,1,0,Rows,Cols)
Then hit the Add button.
The criteria for this formula are as follows:
=OFFSET(reference, rows, cols, height, width),
so Excel goes to the "Start" cell, goes down 1 row, across 0 columns, and selects the range starting at that intersection and continuing on for "Rows" down and "Cols" across.
Note: If you have a set number of columns, as described in step 1 above, you could use the formula =Offset(Start,1,0,Rows,2) where 2 is the standard number of columns.
This should help you achieve a changeable range size. I'm assuming that you will re-run the macro each time you want to generate the chart - this lets you use a reference to this "Float" range in the macro and have it always refer to the current version of this range when creating the new chart
FYI, if you are just "recording" the macro as you create a chart, you will need to go into the Tools / Macro / Visual Basic Editor and change the range that it has set during recording (i.e.,
Code:
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:A45"), PlotBy :=xlColumns
Should be changed to
Code:
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("Float"), PlotBy :=xlColumns