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.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
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

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")





"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
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

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

"Patrick Simonds" wrote in message
...
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")





"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Patrick Simonds" wrote in message
...
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

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:

TEXT() evaluated strings into themselves -- sheet renamed
TEXT() evaluated non-negatiive numbers into a date format -- sheet renamed
TEXT() evaluated negative numbers to #VALUE -- sheet not renamed
TEXT() evaluated something into an already existing sheet name -- sheet not renamed