Code will not run

[Patrick C. Simonds]

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

 

[Gary Keramidas]

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.

 

[Patrick C. Simonds]

B4 contains a date. The date on each sheet is different

 

[Patrick C. Simonds]

I should add that if I type text into AB1 the code works. But I need to be
able to rename each sheet based on the date on the sheet.

 

[Bob Phillips]

The problem is that you are trying to activate a worksheet by name, having
changed the saved name. As you don't activate the sheet as you go through,
so you can ditch that bit altogether.

Sub Rename_Worksheets()
'This code runs to rename the worksheets

Dim Sh As Worksheet

Const sStr As String = "AB1"

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

Exit Sub
ErrHandler:
MsgBox "Cell " & sStr & ", " & Sh.Range(sStr).Text & ", on sheet " &
Sh.Name & " is not valid sheet name"
Resume Next

End Sub

 

[Dave Peterson]

Another problem could be that the value in AB1 is a date. With my USA settings,
that .value would be equal to something like:

11/12/2008

And worksheet names can not have slashes in them.

If you've already formatted the cells nice (and legal), you could use:

Sh.Name = Sh.Range(sStr).Text 'what appears in the cell, not the .value

or you could format it the way you like:

Sh.Name = format(Sh.Range(sStr).Value, "dd mmm yyyy") 'I like 4 digit years!

 

[Patrick C. Simonds]

And example of a value that appears in cell AB1 would be 10 Nov 08. That
is achieved by the the formula located in cell AB1 "=TEXT(B4,"dd mmm yy")".

What did you mean by "If you've already formatted the cells nice (and
legal)"? How should the cell be formatted?

 

[Dave Peterson]

In your case, the value in that cell is really the string "10 Nov 08".

But if you had a real date in that cell and it was formatted to show "10 Nov
08", then the value would be the real date (11/10/2008 for me with my
settings)--not the string that you see.

So there's a difference between the .text property (what you see) and the .value
property (what you may see in the formula bar.

I can format a date (11/10/2008) to show November 10, 2008 in the cell. But the
..value is 11/10/2008.

The .value isn't a legal name (since it contains the slashes (with my USA
settings)).

The .Text is ok.

If I formatted the date to show:
Monday November 10, 2008
(with all those extra spaces)

Then that wouldn't be a valid worksheet name, either--since it's longer than 31
characters.