Still looking for a solution for renaming worksheets

[Patrick Simonds]

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

 

[Tom Ogilvy]

First you say it doesn't work. Then you say it does. Maybe if you offered
a clearly stated question, someone would provide the assistance you may or
may not need.

 

[Patrick Simonds]

My apologies was not intending to confuse the issue.

The code does cycle through all the worksheets but does not rename the
worksheets when cell AB1 contains:

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

 

[NickHK]

Patrick,
And what does "=TEXT($A$4,"dd mmm yy")" evaluate to ?

Contain any illegal characters or otherwise does not follow WS name
requirements ?

NickHK

 

[Randy Harmelink]

I just tried the macro as you've stated it and it worked fine for me.
I tried a number of different values in cell A4: