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

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.



--


Gary

"Patrick C. Simonds" wrote in message
...

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

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


"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
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.



--


Gary

"Patrick C. Simonds" wrote in message
...

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

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.

"Patrick C. Simonds" wrote in message
...
B4 contains a date. The date on each sheet is different


"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
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.



--


Gary

"Patrick C. Simonds" wrote in message
...

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

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




--
__________________________________
HTH

Bob

"Patrick C. Simonds" wrote in message
...

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

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" wrote:

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

--

Dave Peterson

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

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

--

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.



"Patrick C. Simonds" wrote:

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

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

--

Dave Peterson

--

Dave Peterson