Check if worksheet exists and delete it and then add it again

[Waimea]

Hi,

I am trying to automate the creation of a worksheet. I want to check if a file exists, if yes, delete the file, if no, create the file.

This is the code I currently have:

Dim sheet As Worksheet
Application.DisplayAlerts = False
Sheets("Test").Delete
Application.DisplayAlerts = True
Set sheet = Sheets.add
sheet.Name = "Test"

 

[Waimea]

I guess I need an if statement but I am not sure how to use it here?

 

[charlesdh]

Hi,

See if this works for you. It loops thru the worksheet to see if it exist. If not it will add the sheet. This assumes you want a "Worksheet" and not a "File" as you indicated.

Sub sheet_Test()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "Test" Then
        Exit Sub
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Test"
    End If
Next ws
End Sub

‹

 

[Chihiro]

Or you could just add On Error Resume Next...

Sub Demo()
Dim sheet As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Test").Delete
Application.DisplayAlerts = True
Set sheet = Sheets.Add
sheet.Name = "Test"
End Sub

But if you want to test if worksheet exists.
Use IsObject to check if worksheet is an object within the workbook.

https://chandoo.org/forum/threads/getting-vba-to-reference-the-correct-worksheet.12948/#post-76321

 

[Waimea]

Thank you, both ways work!

 

[charlesdh]

Or you could just add On Error Resume Next...

Sub Demo()
Dim sheet As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Test").Delete
Application.DisplayAlerts = True
Set sheet = Sheets.Add
sheet.Name = "Test"
End Sub

But if you want to test if worksheet exists.
Use IsObject to check if worksheet is an object within the workbook.

https://chandoo.org/forum/threads/getting-vba-to-reference-the-correct-worksheet.12948/#post-76321

Ah! I left out the "Delete" if "Exist". Thank for the catch.