Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

Tom’s Tutorials For Excel: Does a Worksheet Exist, and Create One if Not?

There are three scenarios usually associated with this question.

Scenario #1:
Simply yes or no, does a certain worksheet name exist in the active workbook?

Sub TestSheetYesNo()
Dim mySheetName As String, mySheetNameTest As String
mySheetName = "Sheet4"
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).Name
If Err.Number = 0 Then
MsgBox _
"The sheet named ''" & mySheetName & "'' DOES exist in this workbook."
Else
Err.Clear
MsgBox _
"The sheet named ''" & mySheetName & "'' does NOT exist in this workbook."
End If 
End Sub

Scenario #2
Does a certain worksheet name exist in the active workbook, and if not, add a new worksheet with that name?

Sub TestSheetCreate()
Dim mySheetName As String, mySheetNameTest As String
mySheetName = "Sheet4"
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).Name
If Err.Number = 0 Then
MsgBox _
"The sheet named ''" & mySheetName & "'' DOES exist in this workbook."
Else
Err.Clear
Worksheets.Add.Name = mySheetName
MsgBox _
"The sheet named ''" & mySheetName & "'' did not exist in this workbook but it has been created now !"
End If
End Sub

Scenario #3
Add a new worksheet with a certain name, whether or not that worksheet name currently exists.

In this scenario, if a worksheet named (for this example) Sheet4 exists, you need to delete it, then create a new worksheet named Sheet4.

Sub CreateSheet()
Dim mySheetName As String
mySheetName = "Sheet4"
Application.DisplayAlerts = False
On Error Resume Next
Worksheets(mySheetName).Delete
Err.Clear
Application.DisplayAlerts = True
Worksheets.Add.Name = mySheetName
MsgBox "The sheet named ''" & mySheetName & "'' has been created."
End Sub