How can I find out if a sheet exists with if else

Question

I need to know if a sheet already exists, if it exists I want to be able to replace it.
If it does not exist then create it.

On Error Resume Next
Set sht = ThisWorkbook.Sheets("someSheet")
On Error GoTo 0

When I use the above I can use the below if the sheet already exist

If Not sht Is Nothing Then

else

end if

But if it does not exist sht becomes empty and the if fails, displaying Run-time Error 424: Object Required.

How can I set up a if else to know whether the sheet exists or not?

I'm very surprised to see that the accepted answer in that question works. It uses the same logic in the end of the function as my if. — Andreas, Oct 28 '21 at 06:13
I wanted to add a `tryGetWorksheet`-function as answer - which would have helped you more and is not part of the answers of the other thread - but was too late. Maybe you can ask to reopen the question. — Ike, Oct 28 '21 at 06:20
Sometimes it is a good idea (to attempt) to delete the sheet and add a new one: `Application.DisplayAlerts = False: On Error Resume Next: ThisWorkbook.Sheets(someSheet).Delete: On Error Goto 0: Application.DisplayAlerts = True: Set ws = ThisWorkbook.Worksheets.Add: ws.Name = "someSheet"`. — VBasic2008, Oct 28 '21 at 06:31
@ike I can't reopen. I can only vote to reopen. I don't believe it will be reopened, and it shouldn't in my opinion since the question has answers in the other thread. You could add the answer in the other thread though. It is most likely a more active thread and where answers should be in my opinon. — Andreas, Oct 28 '21 at 06:38
Adding answer no 24 after 10 years doesn't make sense to me ... plus the scope of a tryGet concept is slightly different to just checking the existance. — Ike, Oct 28 '21 at 06:45

score 1 · Answer 1 · answered Oct 28 '21 at 06:08

Try below codes.

Sub shExist()
On Error GoTo ErrTrack
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Sheet4")

CreateSheet:
'Code to create sheet here
MsgBox "Create your own sheet"


Exit Sub
ErrTrack:
If Err.Number = 9 Then
    MsgBox "No such sheet exist"
    Resume CreateSheet
End If
End Sub

How can I find out if a sheet exists with if else

1 Answers1