Looking to tweak a macro that renames a chart

[aa123]

I'm currently in the middle of a project to create chart reports for multiple users.

Upon pressing a macro button, what I'd like it to do is the following:

- Create a chart
- Change the chart title
- Export the chart as an image

With the code I have, to change the chart title I need to input the chart number, but upon creating a chart it's assigned it's own number. Is there any way I can change the code so that it picks up the number of the most recently generated chart?

Here's the chart rename code:

ActiveSheet.ChartObjects("Chart ##").Activate
ActiveChart.ChartArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Range("a44").Value & " Conversion Rate"

I also have code for identifying the number of the chart, could this be used in some way?

Sub IdentifyChart()
Dim chtObject As ChartObject
For Each chtObject In ActiveSheet.ChartObjects
MsgBox chtObject.Name
Next
End Sub

Thanks in advance for any help.

 

[VoG]

Try

ActiveSheet.ChartObjects(ChartObjects.Count).Activate

 

[aa123]

I pasted it over the "ActiveSheet.ChartObjects("Chart ##").Activate" code, and it gives me an error saying:

Run-time error 424':
 
Object required

Any ideas?

I'm using Excel 2003.

 

[VoG]

Sorry, it should be

ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate

 

[aa123]

It works perfectly!

Thanks a lot for your help VoG, much appreciated.