Change a chart name to the sheet tab name?

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
I have approx 60 sheet tab each with a chart for each branch in the company. Is it possible to name the charts to equal the sheet tab? There is also a cell that contains the branch number in A33 on each sheet. I could use that reference as well.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,829
Office Version
  1. 2013
Platform
  1. Windows
Hi Rick,

I have beeen playing around with this macro:
It requires that there is only one chart per sheet and each sheet has a chart.
Please try it on a document copy.

Code:
Sub ChartRename()
 
Dim ws As Worksheet
 
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ActiveSheet.ChartObjects.Select
N = ActiveSheet.ChartObjects.Count
ChrtName = ActiveSheet.ChartObjects(N).Name
ActiveSheet.ChartObjects(ChrtName).Name = ws.Name
 
Next
End Sub
 

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
Thanks. There are other sheets in the wb and a few have multiple charts. The majority of the wb has one chart on each sheet. Is it possible to modify the code to exclude sheet9,7 and 21?
 

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
I ran the code and it renamed the charts as needed then on the sheet without a chart it crashed..... as expected. I then just fixed a couple of chart names...... ALL IS WELL.....
THANKS ---- problem solved.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top