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
Leave a Reply