Thread: Chart source data - VBA

Hi,

How do I define a chart's source data in VBA? Is it possible? I can't seem to find the command to do so

Thanks

Coaster,

start the macro recorder, select some data, create a chart. Select a chart series, change the data source. Stop the macro recorder. Study the code.

This is for Excel 2003. If you use 2007 that won't work.

Please update your profile and indicate your Excel version to take the guesswork out of the answering.

Coaster, unfortunately the Excel 2007 macro recorder ignores a lot of things to do with charting. It gets better again with Excel 2010.

I recorded the code below with 2010
- selected a range, first column has dates, third and second column has numbers
- created a chart (clustered column chart)
- edited each series and...

- defined new data ranges and data series title for two series
- defined new range for X axis (categories)

Hope you can glean something from that.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B11:D20").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Sheet1!$B$11:$D$20")
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$2:$C$5"
    ActiveChart.SeriesCollection(1).Name = "=Sheet1!$C$1"
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!$D$2:$D$5"
    ActiveChart.SeriesCollection(2).Name = "=Sheet1!$D$1"
    ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$B$2:$B$5"
End Sub

cheers