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

Instead of looping through Worksheets, you can test the existence of an item in the collection by trying and getting 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"