CODEWITHSUNDEEP
vbaexcel
tombata
tombata

Reputation: 259

Add sheet with successive number VBA

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?

Upvotes: 1

Views: 218

Answers (1)

Vityata
Vityata

Reputation: 43585

Add this code in the Workbook: enter image description here

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.

Upvotes: 2

Related Questions