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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> B4 contains a date. The date on each sheet is different
>
>
> "Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>
>>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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