Still looking for a solution for renaming worksheets

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

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
 
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.
 
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")
 
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
 
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:
 
Back
Top