add new sheets & incerement numbers using inputbox

[Ali M]

hello

I have first sheet name's MAIN . what I want using input box if i write 1 , then should add new sheet name's PURCHASE1 after the first sheet and if I write 2 in inputbox then should add new sheets names are PURCHASE2,PURCHASE3 and so on . to become the final result after sheet MAIN (PURCHASE1,PURCHASE2,PURCHASE3)

so every time write value into inputbox should add and inceremnts numbers based on previous sheet name.
thanks

 

[mumps]

This macro assumes that your workbook starts out with only one sheet.

Sub AddSheets()
    Application.ScreenUpdating = False
    Dim wsNum As String, x As Long, y As Long
    wsNum = InputBox("Please enter the number of sheet to create.")
    If Sheets.Count = 1 And wsNum = 1 Then
        Sheets.Add(after:=Sheets(Sheets.Count)).Name = "PURCHASE1"
        Exit Sub
    Else
        If Sheets.Count = 1 Then
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = "PURCHASE1"
            For y = 2 To wsNum
                x = Mid(Sheets(Sheets.Count).Name, 9, 9999)
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = "PURCHASE" & x + 1
            Next y
        Else
           For y = 1 To wsNum
                x = Mid(Sheets(Sheets.Count).Name, 9, 9999)
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = "PURCHASE" & x + 1
            Next y
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi,
Couple of ideas

- if you are just adding Purchase sheets in sequence after the "Main" sheet then maybe no need for InputBox & something like this will do what you want

Sub AddPurchaseSheet()
    Dim NextNo As Long
                              
    NextNo = Val(Mid(Worksheets(Sheets.Count).Name, 9)) + 1
    Worksheets.Add(after:=Worksheets(Sheets.Count)).Name = "PURCHASE" & NextNo
End Sub

If though want an Input option & again, assuming Purchase sheets remain in sequence, then may this

Sub AddPurchaseSheet()
    Dim IndexNo     As Variant
    Dim SheetExists As Boolean
    Dim Default     As Long
    
    
    Do
        If SheetExists Then MsgBox "PURCHASE" & IndexNo & Chr(10) & _
                                    "Sheet Name Exists", 48, "Sheet Exists"
                                    
        Default = Val(Mid(Worksheets(Sheets.Count).Name, 9)) + 1
                                    
        IndexNo = InputBox("Enter Next Sheet Number", "Add New Sheet", Default)
        'cancel pressed
        If StrPtr(IndexNo) = 0 Then Exit Sub
        'check if sheet exists
        SheetExists = Evaluate("ISREF('PURCHASE" & Val(IndexNo) & "'!A1)")
    Loop Until Val(IndexNo) > 0 And Not SheetExists
    Worksheets.Add(after:=Worksheets(Sheets.Count)).Name = "PURCHASE" & IndexNo
End Sub

Dave

 

[Ali M]

@mumps
excellent ! much appreciated for your help

 

[mumps]

You are very welcome.

 

[Ali M]

@dmt32 great work !
just the second macro you seem misunderstood when eneter value like 3 . it just add purchase3 , but what I want should add three sheets at once
should be purchase1 purchase2,purchase3 . and if enter value 1 then should add purchase4 , then the final result could be purchase1 purchase2,purchase3,purchase4
anyway thanks for theses choices .