Renaming Worksheet from value in a cell

**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.