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