Excel Chart Source 4

[adamsr]

I need to modify this code, or perhaps try a different code.
I want the macro to move chart source data one cell to the right each time it runs. The source data needs to stay on the original columns and simply add one. For example, source data would be A1:E5 . Next time it runs, I want it to change to A1:F5, after that A1:G5, etc , etc.

s=activechart.SeriesCollection(1).formula
p1=instrrev(s,"!&quot
p2=instrrev(s,",&quot
set cell=sheets("MyData&quot.range(mid(s,p1+1,p2-p1-1)).offset(0,1)
activechart.SeriesCollection(1).formula=left(s,p1)+cell.address+mid(s,p2)

Thanks in advance!

 

[klmorgan]

I use this simple way to achive something like what you need

In the column named Range I set the start of the Range for the chart range in Z2 the Column descriptor in Z2 and I have the increasing number in A2 Then I concatenate Z2,x1,Z3,A2
in Z4 and use the contents of Z4 as my range for the chart by naming it in a macro and setting the chart to that range

The count in a2 can be incremented each time the macro runs but I use a count of the rows that hold data so that the graph increases/decreases according to the data available.

A Z X
1 Row 1 Range
2 37 2 B4 (Start of range) 1 :
3 B
4 B4:B37
Regards

Keith

 

[onedtent]

took a little while to figure this out

this is what i had, u can make changes as u like

cells A1 to A12 names of the months
cells B1 to B12 values

highlight A1:B1 and plot a chart.

In cell E1 type number 1. u can have this any value u like

add this macro to ur workbook

Sub clls()

a = Range("E1&quot.Value
a = a + 1
Range("E1&quot.Value = a

End Sub

in the Excel worksheet
go to Insert, Name, Define
in the Names in Workbook type nms
in refers to box delete whatever is there and type
=OFFSET(Sheet1!$A$1,0,0,Sheet1!$E$1)
click OK


again
go to Insert, Name, Define
in the Names in Workbook type yax
in refers to box delete whatever is there and type
=OFFSET(Sheet1!$B$1,0,0,Sheet1!$E$1)
click OK

click on the series in the chart
in the formula bar it should say
=SERIES(Sheet1!$A$1,,Sheet1!$B$1,1)
change it to read
=SERIES(,Sheet1!nms, Sheet1!yax, 1)
hit enter

now every time u run macro clls, your chart will change

 

[adamsr]

thanks!

 

[rigel7]

I suppose people are stingy with stars. onedtent, here is one for you.