The problem is that you are trying to activate a worksheet by name, having
changed the saved name. As you don't activate the sheet as you go through,
so you can ditch that bit altogether.

Sub Rename_Worksheets()
'This code runs to rename the worksheets

Dim Sh As Worksheet

Const sStr As String = "AB1"

On Error GoTo ErrHandler
For Each Sh In ThisWorkbook.Worksheets
Sh.Name = Sh.Range(sStr).Value
Next Sh

Exit Sub
ErrHandler:
MsgBox "Cell " & sStr & ", " & Sh.Range(sStr).Text & ", on sheet " &
Sh.Name & " is not valid sheet name"
Resume Next

End Sub




--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...

Can someone tell me why this code fails? It triggers the MsgBox. The
contents of cell AB1 is =TEXT(B4,"dd mmm yy"). Also what format should
be given to cell AB1?


Sub Rename_Worksheets()
'
' Macro1 Macro
' Macro recorded 12/19/2005 by Cathy Baker
'

'

'This code runs to rename the worksheets

Dim wks As String
Dim Sh As Worksheet

wks = ActiveSheet.Name

Const sStr As String = "AB1"

On Error GoTo ErrHandler
For Each Sh In ThisWorkbook.Worksheets
Sh.Name = Sh.Range(sStr).Value
Next Sh

Worksheets(wks).Activate

Exit Sub
ErrHandler:
MsgBox "Cell" & sStr & "on sheet" & sh.Name & "is not valid sheet name"
Resume Next

End Sub