Renaming Worksheet from value in a cell

**jartzh** · 07-20-2010, 12:07 PM

I have a workbook with 100 worksheets named (1 - 100). I would like to run a single macro to rename each worksheet from the contents of its Cell B3.

Thanks

**Paul** · 07-20-2010, 12:14 PM

Hi Jartzh, perhaps,

Sub jartzh()
Dim sh as Worksheet
For each sh in ThisWorkbook.Worksheets
    sh.Name = sh.Range("B3").Value
Next sh
End Sub

Note, that this will generate an error if any of the B3 cells contain identical values, or if any of them contain invalide sheet name characters such as [ ] / \ ? :

**jartzh** · 07-20-2010, 01:43 PM

Paul,
I get a msg saying the value of the worksheet will be invalid. I think there are unprintable chars in the cell. How can I strip out the first 6 chars for the name. I tried to add a Left function to your code but it yellowed out.

Thanks

**Paul** · 07-20-2010, 01:46 PM

If you want to KEEP the first six characters and make that the sheet name, use:

Sub jartzh()
Dim sh as Worksheet
For each sh in ThisWorkbook.Worksheets
    sh.Name = Left(sh.Range("B3").Value, 6)
Next sh
End Sub

If you want to get rid of the first six characters, use:

Sub jartzh()
Dim sh as Worksheet
For each sh in ThisWorkbook.Worksheets
    sh.Name = Right(sh.Range("B3").Value, Len(sh.Range("B3").Value) - 6)
Next sh
End Sub

**jartzh** · 07-20-2010, 01:48 PM

Paul,
You are the man!

Works like a champ.

Thanks,

**rhino54** · 08-28-2012, 04:05 PM

Hey Paul,

How would I do this if instead of a copying a value from Cell B3, i were copying a String.

For example, I want to rename each of the tabs a different ticker symbol. Each of these are already in the worksheets cell B3.

**Cutter** · 08-28-2012, 07:29 PM

Hello rhino54, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.