Before you perform operations such as moving, copying, or deleting a worksheet in Excel VBA, it is essential to check whether the target worksheet exists in the given workbook to avoid runtime errors.
If you want to check whether a specific sheet exists in a workbook using VBA, you can do so by looping through all the sheets in the workbook and comparing their names with the sheet you are looking for.
If there is a match, it means that the sheet exists in the workbook. Conversely, the sheet does not exist in the workbook if there is no match.
In this tutorial, I will show you several scenarios where you can apply VBA to check whether a sheet exists in a given workbook.
Scenario #1: Check Whether a Sheet Exists in the Current Workbook
The following Excel VBA code checks whether the worksheet ‘Electronics’ exists in the current workbook:
Sub CheckSheetExists()
Dim sheetName As String
Dim sheetExists As Boolean
Dim ws As Worksheet
sheetName = "Electronics"
sheetExists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
MsgBox "Sheet exists in the workbook."
Else
MsgBox "Sheet does not exist in the workbook."
End If
End SubWhen you execute the code, and the worksheet you are checking for exists in the current workbook, the code returns a message box indicating that the sheet exists in the workbook:
If the sheet you are checking for does not exist in the current workbook, the code returns a message box showing that the sheet does not exist in the current workbook:
Explanation of the Code
The VBA code iterates through each worksheet in the current workbook, checking if the sheet’s name matches ‘Electronics.’
If the code finds a match, it sets the Boolean variable to true and exits the loop.
Finally, it displays a message box indicating whether the sheet exists or not based on the value of the Boolean variable. Notice that the name of the sheet we are checking is hard-coded in the code.
Also read: VBA to Check If Folder Exists
Scenario #2: Ask the User for a Sheet Name and Check if It Exists
Instead of hard-coding a sheet’s name, you can write VBA code that prompts the user to enter the name to check.
You can use the VBA code below to prompt the user to enter the name of a worksheet and check whether the sheet exists in the current workbook:
Sub EnterCheckSheetExists()
Dim sheetName As Variant
Dim ws As Worksheet
Dim sheetExists As Boolean
sheetName = InputBox("Enter the name of the sheet to check:")
If sheetName = Empty Then
Exit Sub
End If
sheetExists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
MsgBox "The sheet exists in the current workbook."
Else
MsgBox "The sheet does not exist in the current workbook."
End If
End SubThis Excel VBA code is similar to the code in Scenario #1, except that it asks the user to enter the name of a worksheet to check.
After the user enters the worksheet’s name to check, the code loops through each worksheet in the current workbook, checking if the sheet’s name matches the one provided by the user.
If the code finds a match, it sets the Boolean variable to true, exits the loop, and displays a message box indicating whether the sheet exists based on the value of the Boolean variable.
Notice that if the user cancels the input box, the subroutine ends.
Also read: VBA to Copy Sheet to New Workbook
Scenario #3: Check Whether a Sheet Exists in Another Open Workbook
You can use the VBA code below to check if a sheet exists in another open workbook:
Sub CheckSheetAnotherOpenWorkbook()
Dim sheetName As String
Dim ws As Worksheet
Dim sheetExists As Boolean
Dim targetWorkbook As Workbook
Dim wbName As String
sheetName = InputBox("Enter the sheet you want to check:", _
Title:="Check Sheet")
wbName = InputBox("Enter the workbook in which you want to check:", _
Title:="Workbook in Which to Check")
Set targetWorkbook = Workbooks(wbName)
sheetExists = False
For Each ws In targetWorkbook.Worksheets
If ws.Name = sheetName Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
MsgBox "The sheet exists in the target open workbook."
Else
MsgBox "The sheet does not exist in the target open workbook."
End If
End SubThis VBA code prompts the user to input the name of a worksheet and the name of an open workbook. It then checks if the specified worksheet exists within the target open workbook.
If the worksheet exists, the code displays a message indicating the sheet exists; otherwise, it informs the user that it does not exist in the target workbook.
Note: You can hard-code the sheet to check and the workbook in which to check instead of prompting the user to input them.
Also read: VBA to Add New Sheet
Scenario #4: Check Whether a Sheet Exists and Create It If It Doesn’t
The example VBA code below checks whether the sheet ‘Electronics’ exists in the current workbook.
If the sheet exists, the code displays a message box indicating that the sheet exists. If the sheet does not exist, the code creates a new worksheet with the name ‘Electronics’ in the current workbook:
Sub CheckSheetExistsCreate()
Dim sheetName As String
Dim sheetExists As Boolean
Dim ws As Worksheet
Dim NewWs As Worksheet
sheetName = InputBox("Enter the name of the sheet to check:", _
Title:="Check Sheet")
If sheetName = Empty Then
Exit Sub
End If
sheetExists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
MsgBox "The sheet exists in the current workbook."
Else
Set NewWs = Worksheets.Add
NewWs.Name = sheetName
MsgBox "The sheet was non-existent. A new sheet named " & sheetName & " has been created."
End If
End SubThis VBA code asks the user to input the name of a worksheet. It then checks if a worksheet with that name exists within the current workbook.
If the worksheet exists, it displays a message indicating it exits.
If the worksheet does not exist, it creates a new worksheet with the inputted name and notifies the user that a new sheet has been created.
Scenario #5: Open a Closed Workbook and Check Whether it Contains a Specific Sheet
Below is the code that will open a closed workbook and check whether a specific sheet exists in it or not.
Sub OpenClosedWorkbookCheckSheet()
Dim sheetName As String
Dim workbookPath As String
Dim ws As Worksheet
Dim sheetExists As Boolean
Dim targetWorkbook As Workbook
sheetName = InputBox("Enter the name of the sheet to check:", _
Title:="Check Sheet")
workbookPath = Application.GetOpenFilename(Title:="Select the workbook", FileFilter:="Excel Files *.xls* (*.xls*),")
If workbookPath = "False" Then Exit Sub
Application.ScreenUpdating = False
Set targetWorkbook = Workbooks.Open(workbookPath, ReadOnly:=True, UpdateLinks:=False)
sheetExists = False
For Each ws In targetWorkbook.Worksheets
If ws.Name = sheetName Then
sheetExists = True
Exit For
End If
Next ws
targetWorkbook.Close False
Application.ScreenUpdating = True
If sheetExists Then
MsgBox "The sheet exists in the target workbook."
Else
MsgBox "The sheet does not exist in the target workbook."
End If
End SubThis VBA code prompts the user to provide the worksheet’s name and the workbook’s path. It opens the specified workbook in read-only mode without updating links or displaying prompts.
The code then checks whether the specified worksheet exists in the opened workbook.
Once the code completes the check, it closes the workbook and informs the user whether the sheet exists in the target workbook.
In this tutorial, I showed you five scenarios of checking whether a sheet exists in a given workbook. I hope you found the tutorial helpful.
Other Excel articles you may also like:
