Add sheet with successive number VBA

Question

I have a simple macro, that adds a sheet to a workbook. However, sometimes I have to delete some sheet(s). When I delete a sheet and I use the macro again, the newly added sheet assumes the number which follows, as if the deleted sheets are stil present. I want that the macro adds a sheet with the next number. For example I have:

Sheet1 Sheet2 Sheet3

Then I delete Sheet3 and want to add another sheet, which perfectly will be Sheet3 and not Sheet4

With ThisWorkbook

    .Sheets.add After:=.Sheets(.Sheets.Count)

End With

How can I do this?

Off the top of my head, you would need to loop through the existing sheets to check their names, find the missing sheet in the iteration, and set the new name to that missing sheet. Then add the sheet as normal with "Name:=SheetName". — Brandon Barney

Vityata Vityata · Accepted Answer · 2017-02-21T12:31:37

Add this code in the Workbook:

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    On Error GoTo Workbook_NewSheet_Error

    Sh.Name = "Sheet" & Sheets.Count

    On Error GoTo 0
    Exit Sub

Workbook_NewSheet_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Workbook_NewSheet of VBA Document DieseArbeitsmappe"

End Sub

Pretty much, if you have a the following sheets Sheet1, Sheet2 and Sheet4 and you add a new one you would get an error, because the name of the sheet is taken (Sheet4). That is why I have added error handler.

Add sheet with successive number VBA

1 Answers