what's in B4?

if AB1 equates to the same value on 2 sheets, you'll get an error because 2
sheets can't have the same name.



--


Gary

"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