Macro To Rename Sheets

[Moogie]

Greetings,
The following macro renames the active sheet in a workbook to what is in cell A1 of that active sheet. I need this macro to work on each sheet in my workbook.
Dim MyName
MyName = ActiveSheet.Range("A1").Value
ActiveSheet.Name = MyName
Any help is greatly appreciated.
Thanks

 

[Joe Was]

You cannot name any sheets with a name that is used already! Only one sheet per name? JSW

 

[Moogie]

Greetings Joe,
Guess I should explain this a bit. There will be about 500 sheets in the workbook. Each sheet's A1 value will be a different name. I just need this macro to rename each sheet whatever value is in that sheet's A1 cell.
Thanks in advance for your help.

 

[Joe4]

Try this code:

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Name = Sheets(WS.Name).Range("A1").Value
Next WS

Hope this helps.

 

[Mudface]

Try the following, as Joe said though, if you try and name two sheets the same this will come to a grinding halt....

<pre>
Public Sub RenameSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Range("A1")
Next ws

End Sub
</pre>

 

[Mudface]

Actually, bearing that in mind, and the possibility of using an illegal name, the following might be better: -
<pre>
Public Sub RenameSheets()
Dim ws As Worksheet

On Error GoTo ErrTrap
For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Range("A1")
Next ws

On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox "Unable to rename sheet " & ws.Name & " to " & ws.Range("A1")
Resume Next

End Sub
</pre>

 

[Moogie]

Thank you very much jmiskey and Mudface.
This works beautifully!