Chart Copy, Activate and Change Data Source

[Mr. Wizard]

I am using Excel 2007.
I have a chart with complex formatting that the users of my spreadsheet cannot make themselves. I would like to use a macro to copy the existing chart which is embedded in a spreadsheet, and copy it and the surrounding cells to another worksheet tab, select the chart and then select another data range so that it reflects the user's data rather than the dummy data I used to build the original chart.

My problem is that whenever I generate new charts using this method, I cannot determine the chart name identifier to use to select the chart.


My current method is to select the range of cells containing the data and the chart, and copy it to another range of cells where I paste in the user's data. But in doing this the macro gets lost if I cannot identify the chart name explicitly since the user may also have made other charts.

Is there a way to determine what a chart name will be when it is created by pasting a copy of the chart?

 

[Jon Peltier]

The chart just pasted is the last chart on the sheet. It's activesheet.chartobjects(activesheet.chartobjects.count).chart

Activate it like so:

activesheet.chartobjects(activesheet.chartobjects.count).chart.chartarea.select