vba code to update chart x axis labels

[Elvis]

Dear all,

Wondering if anyone can help me out with a little code I need or suggestion. I created the macro below to update a chart with the latest data and this runs fine. However, despite several attempts, I can't seem to get it to update the X-axis labels as well.


Sub Update_Chart()
ActiveSheet.ChartObjects(1).Activate
Set startseries = Range("e770").End(xlUp).Offset(-50, 0)
Set endseries = Range("e770").End(xlUp)
ActiveChart.SetSourceData Source:=Sheets("table").Range(startseries, endseries), PlotBy:= _
xlColumns
End Sub

For instance, assume that my data runs from E5:E300 and then the data increases to E310. I run the macro and it updates the actual series but the x axis slabels still only reads from E5:E300. I then manually need to update the X axis values (effectively a date feild). Is there anyway code I can add to get this to update as well?

Thanks,

Elvis

 

[tusharm]

You can do this w/o code. See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Alternatively, if you must use code, use the macro recorder (Tools | Macro > Record new macro...) to get the necessary code.

 

[Elvis]

Indeed, I am looking for a VBA solution.

The systax for the chart using the macro recorder is:

ActiveChart.SetSourceData Source:=Sheets("table").Range("A4:A8,C4:C8"), _
PlotBy:=xlColumns

Based on this I'vbe updated the macro to include the additional code for the X axis. However, although the new macro below runs, it appears to plot mutiple datat series. In fact I get the message "The maximum number of data points you can use in a data series for a 2-D chart is 32,000....".

Any idea what I have done wrong?

Thanks,

Elvis


Sub Update_Chart2()
ActiveSheet.ChartObjects(1).Activate
Set startseries = Range("e770").End(xlUp).Offset(-50, 0)
Set endseries = Range("e770").End(xlUp)
Set startlabel = startseries.Offset(0, -4)
Set endlabel = endseries.Offset(0, -4)
ActiveChart.SetSourceData Source:=Sheets("table").Range(startlabel & ":" & endlabel, startseries & ":" & endseries), PlotBy:= _
xlColumns
End Sub

 

[Elvis]

Hello,

Did anyone have any thoughts on my problem?