Sep 03 2020 08:58 AM
Sep 03 2020 09:37 AM
For example:
Sub RenameSheets()
Dim wsh As Worksheet
For Each wsh In Worksheets
wsh.Name = Replace(wsh.Name, "Sheet", "Rob")
Next wsh
End Sub
Sep 03 2020 10:27 AM
Sep 03 2020 12:59 PM - edited Sep 03 2020 01:00 PM
Will the sheets be named Sheet1 etc. when you run the macro? If so:
Sub RenameSheets()
Dim NewName As String
Dim wsh As Worksheet
NewName = InputBox("Enter the new name")
If NewName = "" Then
Beep
Exit Sub
End If
For Each wsh In Worksheets
wsh.Name = Replace(wsh.Name, "Sheet", NewName)
Next wsh
End Sub
If the sheets have already been renamed, we can prompt for the old and new names:
Sub RenameSheets()
Dim OldName As String
Dim NewName As String
Dim wsh As Worksheet
OldName = InputBox("Enter the old name, for example Sheet")
If OldName = "" Then
Beep
Exit Sub
End If
NewName = InputBox("Enter the new name, for example Rob")
If NewName = "" Then
Beep
Exit Sub
End If
For Each wsh In Worksheets
wsh.Name = Replace(wsh.Name, OldName, NewName)
Next wsh
End Sub
Sep 03 2020 01:22 PM
Sep 03 2020 02:25 PM
Have you tried the second macro from my previous reply?
Sep 03 2020 03:21 PM
Sep 04 2020 03:40 AM
SolutionLike this:
Sub RenameSheets()
Dim OldName As String
Dim NewName As String
Dim wsh As Worksheet
Dim p As Long
NewName = InputBox("Enter the new name")
If NewName = "" Then
Beep
Exit Sub
End If
For Each wsh In Worksheets
OldName = wsh.Name
For p = Len(OldName) To 1 Step -1
If Not IsNumeric(Mid(OldName, p, 1)) Then
Exit For
End If
Next p
wsh.Name = NewName & Mid(OldName, p + 1)
Next wsh
End Sub
Sep 04 2020 09:53 PM
Sep 05 2020 03:13 AM
You might use a macro to insert a new sheet:
Sub NewSheet()
Dim wsh As Worksheet
Dim OldName As String
Dim p As Long
Dim n As Long
Dim m As Long
For Each wsh In Worksheets
OldName = wsh.Name
For p = Len(OldName) To 1 Step -1
If Not IsNumeric(Mid(OldName, p, 1)) Then
Exit For
End If
Next p
n = Val(Mid(OldName, p + 1))
If n > m Then m = n
Next wsh
Worksheets.Add.Name = "Sheet" & m + 1
End Sub
You can assign the macro to a custom keyboard shortcut and/or to a Quick Access Toolbar button.
Sep 04 2020 03:40 AM
SolutionLike this:
Sub RenameSheets()
Dim OldName As String
Dim NewName As String
Dim wsh As Worksheet
Dim p As Long
NewName = InputBox("Enter the new name")
If NewName = "" Then
Beep
Exit Sub
End If
For Each wsh In Worksheets
OldName = wsh.Name
For p = Len(OldName) To 1 Step -1
If Not IsNumeric(Mid(OldName, p, 1)) Then
Exit For
End If
Next p
wsh.Name = NewName & Mid(OldName, p + 1)
Next wsh
End Sub