vba code to update chart x axis labels

Elvis

New Member
Joined
Apr 23, 2006
Messages
45
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Elvis

New Member
Joined
Apr 23, 2006
Messages
45
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
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top