Add a new consecutive sheet

[Visor]

greetings friends of the forum, how can I do to add new sheet that has consecutive value from an existing sheet
Example I have the sheet Unit_1
I want the sheet Unit_2 to be added
for each click on the command button a new sheet with the following value must be added Unit_3, Unit_4 ....
I hope it can be resolved, I leave a file for you to see, ... I have tried several ways
I express my anticipated thanks

 

[AlanSidman]

Option Explicit

Sub AddWorksheets()
    Dim i As Long
    Dim Last As Long
    Dim sname As String
    sname = "Sheet"  'change this as necessary
    Last = InputBox("How many sheets to add?")
    For i = 1 To Last
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = sname & i
    Next i
End Sub

 

[Visor]

Thanks I think it is in the way of the solution, what happens is that, the first time I click the button and I put 1 in the input box, I created the sheet Unit_1, even if it already exists.
I close the userform
Again I use the macro and again I put 1 to the input and I am wrong because the sheet already exists.
What I want is to take into account the sheet that already exists and is numbered must take into account the last number to add sheet follow the consecutive number in advance

 

[Kenneth Hobson]

I used dash character to delimit base sheet name from suffix number. You should be able to modify this easily enough.

e.g.

Sub Test_NextWSname()
  MsgBox NextWSname(ActiveSheet.Name, ThisWorkbook.Name)
End Sub

Function NextWSname(wsName As String, wbName As String) _
  As String
  Dim i As Integer, s As String
  s = wsName
  If InStr(s, "-") <> 0 Then _
    s = Left(s, InStrRev(s, "-") - 1)
  wsName = s
  Do Until Not WorkSheetExists(wsName, wbName)
    i = i + 1
    wsName = s & "-" & i
  Loop
  NextWSname = wsName
End Function

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

 

[Visor]

Thanks for the support,
I'm sorry, I really do not know where to replace the word "Unit" to find the operation of this macro. So that the result is that for each click I add a sheet.
Click -> Sheet --> Unit_2
Click -> Sheet --> Unit_3
...
Etc

 

[Visor]

maybe es best if the macro ask me Whats name?
if the name already exists, let me know
Whats is the way in this case?

 

[AlanSidman]

Maybe this is the alternative you need?

Option Explicit

Sub AddWorksheet()
    Dim sname As String
    sname = InputBox("What name to use?")
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sname
End Sub

 

[Visor]

Yes!!! It's simple code and it works, I think the other case Microsoft must implement something for this case because I see that there is no solution, just as when it is correlative in a row
Thanks for this solution to the topic