Macor to update Chart Data Range

[rdw72777]

I've been spinning my wheels a bit on this one. Bacially, I have a chart in my workbook for which I'd like to update the data range via macro. I searched the site and the code to add a chart was pretty basic so i figured I'd just record the changes I want to make and begin working with that.

This is the code I recorded:

Sub Macro16()
'
Sheets("sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Sheets("sheet2").Range( _
    "A7,A34:A54,C7:H7,C34:H54")
End Sub

I hate the Select/Activate actions as much as anyone, but what I found odd was, even though this is recorded code, if I try to run the macro it doesn't work. On the "Chart 1.Activate" line I get the error "The item with the specified name wasn't found"

The code for adding new charts seems to work, but my existing charts have so much formatting and manual changes (color schemes and etc.) that I would MUCH rather update an existing chart's data range than build one from scratch via code.

I'm wondering if the fact that this is a whole tab chart (i.e. the kind you do when right-clicking on a worksheet tab and selecting Insert-Chart, not the kind tyou insert fromthe Insert menu in the ribbon) has something to do with this.

Any thoughts on why the recorded code doesn't work?

 

[Andrew Poulsom]

Do you have a ChartObject named Chart 1 on Sheet1?

 

[rdw72777]

Actually, when i click the chart on sheet 1 I don't see any name in the named ranges box. I have no idea where "Chart 1" comes from, but the macro records it as "Chart 1". But how do i identify this chart then....

 

[rdw72777]

I think I figured out, on these types of Chart Sheets, it looks like you can just eliminate the selection of a chartobject since the whole sheet is a chart object.

thanks,