I need code which will take the value of cell AB1 and rename the worksheet
with that value. In cell AB1 is the following:

=TEXT($A$4,"dd mmm yy")

A number of people have offered their solutions but none of them actually
renames each worksheet. I have found that if I enter text in cell AB1 the
worksheet is renamed. here is the code I am using any help would be greatly
appreciated:

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

'

Dim wks As String
Dim sh As Worksheet, sh1 As Worksheet
Const sStr As String = "ab1"

'Application.ScreenUpdating = False

wks = ActiveSheet.Name

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

Worksheets(wks).Activate

sh1.Activate

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

'Application.ScreenUpdating = True


End Sub