Delete worksheet if it exists and create a new one
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"
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"