Forum Discussion

Brass Contributor

Sep 03, 2020

Solved

Renaming the only the sheet

There are many sheet tabs in excel for say sheet1 sheet2 sheet3 sheet 4, sheet5 and so on...however i have deleted sheet2 ..the remaining will be sheet1, sheet3, sheet4,sheet5 and so on.. After ren...

Macros and VBA

HansVogelaar

Sep 04, 2020

Raj_123

Like 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

Raj_123

Brass Contributor

Sep 04, 2020

Waiting for your reply friend

HansVogelaar

MVP

Sep 04, 2020

Raj_123

Like 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

HansVogelaar

MVP

Sep 05, 2020

Raj_123

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.

Raj_123
Brass Contributor
Sep 05, 2020

Home Microsoft Learn

Select the language
(English) English
Click here to refresh translation
Skip to Topic Message
New sheet tab creation issue
Raj_123
Raj_123NEW CONTRIBUTOR
7 hours ago

New sheet tab creation issue
There are 15 tabs in the excel sheet( it is an already saved excel sheet on the desktop as ABC.xlsx). Out the 15 sheet tabs, i have deleted sheet4,sheet6,sheet11and sheet 13.

Now my sheet selection is on sheet2.when i click on new sheet option(+) the new sheets created are sheet4,sheet6, sheet11 and sheet13, i want the new sheets to start from sheet16 instead it is creating all the old deleted sheets first..why?