P
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
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