New sheet tab creation issue in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1637773%22%20slang%3D%22en-US%22%3ENew%20sheet%20tab%20creation%20issue%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637773%22%20slang%3D%22en-US%22%3ENew%20sheet%20tab%20creation%20issue%3CBR%20%2F%3EThere%20are%2015%20tabs%20in%20the%20excel%20sheet(%20it%20is%20an%20already%20saved%20excel%20sheet%20on%20the%20desktop%20as%20ABC.xlsx).%20Out%20the%2015%20sheet%20tabs%2C%20i%20have%20deleted%20sheet4%2Csheet6%2Csheet11and%20sheet%2013.%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20my%20sheet%20selection%20is%20on%20sheet2.when%20i%20click%20on%20new%20sheet%20option(%2B)%20the%20new%20sheets%20created%20are%20sheet4%2Csheet6%2C%20sheet11%20and%20sheet13%2C%20i%20want%20the%20new%20sheets%20to%20start%20from%20sheet16%20instead%20it%20is%20creating%20all%20the%20old%20deleted%20sheets%20first..why%3F%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20help%20me%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1637773%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637960%22%20slang%3D%22en-US%22%3ERe%3A%20New%20sheet%20tab%20creation%20issue%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637960%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780831%22%20target%3D%22_blank%22%3E%40Raj_123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20might%20use%20a%20macro%20to%20insert%20a%20new%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20NewSheet()%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20OldName%20As%20String%0A%20%20%20%20Dim%20p%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20For%20Each%20wsh%20In%20Worksheets%0A%20%20%20%20%20%20%20%20OldName%20%3D%20wsh.Name%0A%20%20%20%20%20%20%20%20For%20p%20%3D%20Len(OldName)%20To%201%20Step%20-1%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20IsNumeric(Mid(OldName%2C%20p%2C%201))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20p%0A%20%20%20%20%20%20%20%20n%20%3D%20Val(Mid(OldName%2C%20p%20%2B%201))%0A%20%20%20%20%20%20%20%20If%20n%20%26gt%3B%20m%20Then%20m%20%3D%20n%0A%20%20%20%20Next%20wsh%0A%20%20%20%20Worksheets.Add.Name%20%3D%20%22Sheet%22%20%26amp%3B%20m%20%2B%201%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20assign%20the%20macro%20to%20a%20custom%20keyboard%20shortcut%20and%2For%20to%20a%20Quick%20Access%20Toolbar%20button.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor
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?

Please help me
1 Reply
Highlighted

@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.