Name charts using VBA (Excel 2003)

Topic

New Reply

I have 94 chart worksheets in an Excel file. I want to ‘name’ the charts the same name as their worksheet name. How do I iterate through all of them applying this name to the sheets.

Thank you for the help.

Reply | Quote

Replies

Sheets must have unique names, a chart sheet cannot have the same name as a worksheet. Or do I misinterpret your question?

Reply | Quote

oh, sorry. I mean the actual chart on the chart sheet. I need to “name” this chart because it gets paste linked into a powerpoint file and I need to access the chart by it’s name in powerpoint.

Reply | Quote

No, my fault. I didn’t read your post carefully enough. The following code will rename the first chart object on each worksheet:

Sub NameCharts()
Dim wsh As Worksheet
On Error Resume Next
For Each wsh In ActiveWorkbook.Worksheets
wsh.ChartObjects(1).Name = wsh.Name
Next wsh
Set wsh = Nothing
End Sub

The On Error Resume Next avoids problems with sheets that don’t contain a chart.

Reply | Quote