Macro Coding for GETCHARTDATA - help?

[excel_guy123]

Hi guys,

Am hoping someone here can help me... I have a worksheet, from which I have created a number of charts that sit in another worksheet.

At the moment the data sitting in the data worksheet is Feb to September, but next month it needs to be Feb to October.

At the moment, I have a chart that pulls data range:

=Master!$C$1:$K$13

And then the macro needs to automatically offset the data (0, 1) - i.e. CHARTDATA needs to be $C$1:$L$13, then $C$1:$M$13.

The offset needs to happen every time I run the macro.. I've been toying with a couple of bits of code, to no avail - hoping someone can advise... this is what I have so far:

Sub Macro2()

Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Set ChartData = Range(Range("D2").Offset(0, 1).End(xlToRight))
ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=ChartData

End Sub

If anyone has any suggestions I would be most grateful...

 

[Derek Brown]

Hello and welcome to The Board.
You don't need to do this with a macro as it is not difficult to make your chart 'dynamic'.
A very good site for help with charts is www.peltiertech.com
In particular go to the following link and look for those articles about 'dynamic' charts:
http://peltiertech.com/Excel/Charts/ChartIndex.html