Set Source Data for Scatter Chart

[ryanpetersen]

Hi,
I'm trying to find a way to use VBA to set the data source range in a scatter chart.
At the moment the scatter chart uses the range A2:B18 which has been named "chart_range".
When I try and add "chart_range" to the source data field it fails with the following error message "Object doesn't support this property or method".
There are limitations in Excel with using dynamic data source range in a scatter chart but was wondering if anyone might have an alternative solution.

Many thanks for any help

[6StringJazzer]

Please show your actual code.

[Jon_Peltier]

You need to reference the name to the sheet it's on using 'Sheet 1'!chart_range

[ryanpetersen]

thanks for your replies.

Here is the code:
Range("D14:E" & LastRow).Select
ActiveWorkbook.Names.Add Name:="PivotScatterRange", RefersTo:=Selection
ActiveSheet.ChartObjects("Chart 1").SetSourceData Source:=Range("PivotScatterRange")

I think I'm missing the reference back to the sheet (as you mentioned) but am unable to get it right.

[romperstomper]

You're missing a reference to the actual chart:

Please Login or Register  to view this content.

[Jon_Peltier]

... and to the sheet, though if it's the active sheet, it's assumed. Better to be obsessive about it than burned by a bad assumption.

Please Login or Register  to view this content.

[ryanpetersen]

Thank you! It works very well!