Delete worksheet if it exists and create a new one

Question

I want to look through my Excel worksheets and find a sheet with a certain name and delete that sheet if it is found. Afterwards I want to create a sheet after all existing sheets with that name. My code is as follows:

For Each ws In Worksheets
    If ws.Name = "asdf" Then
        Application.DisplayAlerts = False
        Sheets("asdf").Delete
        Application.DisplayAlerts = True
        End
    End If
Next

Sheets.Add(After:=Sheets(Sheets.count)).Name = "asdf"

However this doesn't do both of these actions in one run of the code. If the sheet already exists it will simply delete the sheet and not make a new one like I want it to. I need to run it again for it to create a new one.

How do I fix my code to delete the old sheet if it exists and create a new one?

Answer 1

Remove the End statement, your code terminates after finding and deleting the worksheet asdf.

For Each ws In Worksheets
    If ws.Name = "asdf" Then
        Application.DisplayAlerts = False
        Sheets("asdf").Delete
        Application.DisplayAlerts = True
    End If
Next

Sheets.Add(After:=Sheets(Sheets.count)).Name = "asdf"

Answer 2

Instead of looping through Worksheets, you can test the existence of an item in the collection by trying to get it:

Function GetWorksheet(shtName As String) As Worksheet
    On Error Resume Next
    Set GetWorksheet = Worksheets(shtName)
End Function

If Not GetWorksheet("asdf") Is Nothing Then
    Application.DisplayAlerts = False
    Worksheets("asdf").Delete
    Application.DisplayAlerts = True
End If
Worksheets.Add(After:=sheets(sheets.Count)).name = "asdf"

However, the most straightforward method would be trying to delete the sheet while wrapped in a On Error Resume Next - On Error GoTo 0 "block":

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("asdf").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add(After:=sheets(sheets.Count)).name = "asdf"

Answer 3

I disagree that the "most straightforward" approach is to intentionally generate (and suppress) an error. Personally I'd opt for the loop-to-locate method, only attempting deletion if the object exists.

If you're going to be re-creating the worksheet anyway, this will do the job in almost every case:

Sheets("asdf").Cells.Delete  'deletes all cells in the specified worksheet

---

Safely Delete a Worksheet

If the one-liner (above) doesn't work for you (maybe due to some stubborn background colors/images) then here's a sub that you can call to delete any specified worksheet:

Sub deleteSheet(wsName As String)
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Sheets 'loop to find sheet (if it exists)
    Application.DisplayAlerts = False 'hide confirmation from user
    If ws.Name = wsName Then ws.Delete 'found it! - delete it
    Application.DisplayAlerts = True 'show future confirmations
  Next ws
End Sub

Call it simply like:

deleteSheet "asdf"

...where asdf is the name of the worksheet (tab) to delete.

---

Delete & Replace a Worksheet

Alternatively, calling the sub below will delete and replace the worksheet, giving it the same name and putting it in the same tab position as the original:

Sub resetSheet(wsName As String)
  Dim ws As Worksheet, ws2 As Worksheet
  For Each ws In ThisWorkbook.Sheets        'loop to find sheet (if it exists)
    If ws.Name = wsName Then                'found it!
      Set ws2 = ThisWorkbook.Sheets.Add(ws) 'add new sheet located before old one
      Application.DisplayAlerts = False     'hide confirmation from user
      ws.Delete                             'delete sheet (new one takes its place)
      Application.DisplayAlerts = True      'show future confirmations
      ws2.Name = wsName                     'rename new sheet to old name
      Exit Sub                              'finished! (no need to continue looping)
    End If
  Next ws
End Sub

---

About ThisWorkbook:

I used ThisWorkbook in both subs, which is important when running VBA that modifies other workbooks, or while you have multiple workbooks open at the same time.

Using ThisWorkbook ensures that your code is always referring to the workbook where the code is located that called it.

Without it, for example, if you have two similar files open, and you're looking at one while you're waiting for VBA to run in the other workbook, if the workbook that's "on top" has a worksheet with the name of the one VBA's trying to delete, it will delete the worksheet in the current (active) workbook instead of the intended one.

_{(...and you can't Ctrl+Z to undo VBA!)}