An Excel workbook may consist of several worksheets at the bottom of the Excel screen. Each Sheet may have a different set of the dataset. There are numerous ways of checking if the Worksheet exists in your Workbook. One common method of checking if a sheet exists in Workbook is using manual methods such as keyboard shortcuts (CTRL + Pgdown). However, sometimes there are too many sheets in one Workbook, and thus, manually checking for Sheets may be tedious and time-consuming. Thankfully, the VBA tool got us covered. This article will discuss how to use VBA codes to check if a given sheet exists.
Using the Developer Tool
Steps:
1. Open the Excel application.
2. Open the Workbook with your worksheets.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub check_sheet()
Dim sht As Worksheet
Dim shtName As String
shtName = InputBox(Prompt:="Enter the sheet name", _
Title:="Search Sheet")
For Each sht In ThisWorkbook.Worksheets
If sht.Name = shtName Then
MsgBox "Yes! " & shtName & " is there in the Workbook."
Exit Sub
End If
Next sht
MsgBox "No! " & shtName & "is not there in the workbook."
End Sub
6. Press the F5 key to run the VBA code. A Search Sheet dialogue box will open.
7. In the Search Sheet box, type the name of the Sheet you wish to search and click the OK button.
Using View Code Tool
Steps to follow:
1. Open the Excel application.
2. Open the Workbook with your worksheets.
3. Right-click on one of the shown sheets, and select the View Code button from the menu.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub checksheet()
Dim sht As Worksheet
Dim shtName As String
shtName = InputBox(Prompt:="Enter the sheet name", _
Title:="Search Sheet")
For Each sht In ThisWorkbook.Worksheets
If sht.Name = shtName Then
MsgBox "Yes! " & shtName & " is there in the Workbook."
Exit Sub
End If
Next sht
MsgBox "No! " & shtName & "is not there in the workbook."
End Sub
6. Press the F5 key to run the VBA code. A Search Sheet dialogue box will open.
7. In the Search Sheet box, type the name of the Sheet you wish to search and click the OK button.
VBA code 2 to check if the Sheet exists
Below is another code that can be used to check if a given sheet exists:
Sub checksheet()
Dim sht As Worksheet
Dim shtName As String
Dim i As Long
i = Sheets.Count
shtName = InputBox(Prompt:="Enter the sheet name", _
Title:="Search Sheet")
For i = 1 To i
If Sheets(i).Name = shtName Then
MsgBox "Yes! " & shtName & " is there in the Workbook."
Exit Sub
End If
Next i
MsgBox "No! " & shtName & " is not there in the Workbook."
End Sub
