JimS
07-28-2009, 10:26 AM
The code below renames any sheet that begins with the name "Sheet".
Can this be modified to create a list on another sheet of every new sheet name that this code changes?
In example on a sheet labled "Names" starting in cell A2 would be the first new name of the first sheet that this code changed, and then in cell A3 would be the next new name that gets changed, and so on down column A for every sheet that had a name change by this code.
It somehow would have to write the value of xxx out everytime it renamed a sheet.
Thanks...
Jim
Sub ShtRename()
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
xxx = Empty
If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next
xxx = Split(Split(sht.Range("B5").Value, "<A href="file://\\")(1">\\")(1), ".")(0)
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx
End If
Next sht
Application.ScreenUpdating = True
End Sub
Can this be modified to create a list on another sheet of every new sheet name that this code changes?
In example on a sheet labled "Names" starting in cell A2 would be the first new name of the first sheet that this code changed, and then in cell A3 would be the next new name that gets changed, and so on down column A for every sheet that had a name change by this code.
It somehow would have to write the value of xxx out everytime it renamed a sheet.
Thanks...
Jim
Sub ShtRename()
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
xxx = Empty
If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next
xxx = Split(Split(sht.Range("B5").Value, "<A href="file://\\")(1">\\")(1), ".")(0)
On Error GoTo 0
If Not IsEmpty(xxx) Then sht.Name = xxx
End If
Next sht
Application.ScreenUpdating = True
End Sub