Chart data location changing?

[Roosters]

I have a spreadsheet that has similar data on consecutive worksheets (one sheet for each day) On a "Charts" worksheet I want to change a charts data range from one sheet to another. i.e. in a macro delete an existing chart and then add a new chart of the same name - can I in the code below have the reference =Sheets("1") look at a cell reference with the desired days sheet number in it and when I get to run the macro it gets to this code and adds a new chart with the desired days data? I hope this makes sense as it's 0230 in the morning here and the brain is a bit tired.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("1").Range("A2:J3500"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Daily Chart

 

[SkipVought]

hi,

I have a spreadsheet that has similar data on consecutive worksheets (one sheet for each day)

a HUGE design mistake, as you are now experiencing.

ALL your data ought to be in ONE table with a column for DATE in this particular instance. Otherwise, you will continue to be plagued with the issues related to gathering scattered data for analysis and reporting for years to come in your ongoing endeavors.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]