Tom’s Tutorials for Excel: How to verify a worksheet exists, create if not.
The following examples show how to determine if a worksheet exists, and then how to create or replace the worksheet
Determining if a Worksheet Exists
This example shows how to determine if a worksheet named “Sheet4” exists by using the Name property of the Worksheet object. The name of the worksheet is specified by the mySheetName variable.
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
Creating the Worksheet
This example shows how to determine if a worksheet named “Sheet4” exists. If the worksheet does not exist, is is created using the Add method of the Worksheets object.
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
Replacing the Worksheet
This example shows how to determine if a worksheet named “Sheet4” exists. If the worksheet does exist, it is replaced with a new worksheet of the same name, for cases when you want to start off with a clean slate.
Sub TestSheetReplace() 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 replaced." End Sub
Leave a Reply