How to Search the Sheet Name with VBA in Excel - 3 Examples

This is the sample dataset.

Example 1 – Using Excel VBA to Search the Sheet Name and Display the Search Result

STEPS:

Go to the Developer tab and select Visual Basic. You can also press Alt + F11 to open the Visual Basic window.

Select Insert.
Choose Module.

Enter the code in the Module window.

Sub Search_Excel_Sheet_Name()
Dim ShtName As String
Dim ShtSearch As Boolean
ShtName = InputBox("Enter the Sheet Name:")
If ShtName = "" Then Exit Sub
On Error Resume Next
ActiveWorkbook.Sheets(ShtName).Select
ShtSearch = (Err = 0)
On Error GoTo 0
If ShtSearch Then
MsgBox "Sheet '" & ShtName & "' has been found!"
Else
MsgBox "Sheet '" & ShtName & "' could not be found!"
End If
End Sub

ShtName and ShtSearch are declared as variables. ShtName denotes the sheet name and ShtSearch searches the sheet name. The On Error Resume Next statement is used to run the next statements after the statement that generated the error and the On Error GoTo 0 statement is used to disable any enabled error.

Press Ctrl + S to save the code.
Close the Visual Basic window.
Select Macros in the Developer tab. The Macro window will be displayed.

Select the macro and click Run.

In the message box, enter the sheet name.
Click OK.

Another message box will display the search results:

Example 2 – Search the Sheet Name and Select it with VBA in Excel

STEPS:

Go to the Developer tab and select Visual Basic.

Select Insert.
Choose Module.

Enter the code in the Module window.

Sub Search_and_Select_Sheet()
Dim ShtName As String
ShtName = InputBox("Enter the sheet name:")
If ShtName = vbNullString Then
MsgBox "Cancelled!"
Exit Sub
End If
If ShtSearch(ShtName) Then
Worksheets(ShtName).Activate
Else
MsgBox "Sheet name could not be found!"
End If
End Sub
Function ShtSearch(ShtName As String) As Boolean
Dim wks As Worksheet
On Error Resume Next
Set wks = Worksheets(ShtName)
If Not wks Is Nothing Then ShtSearch = True
End Function

The ShtSearch function checks the existence of the searched sheet in an open workbook. ShtName = vbNullString displays Cancelled! if we close the message box.

Press Ctrl + S to save the code.
Close the Visual Basic window.
Select Macros in the Developer tab. The Macro window will be displayed.

Select the macro and click Run.

Enter the sheet name in the message box and click OK.

If the sheet is found in the open workbook, it will be selected.

Otherwise, the message: Sheet name could not be found! is displayed.

Example 3 – Using Excel VBA to Search the Sheet Name in a Closed Workbook and Display the Search Result

STEPS:

Go to the Developer tab and select Visual Basic.

Select Insert.
Choose Module.

Enter the code in the Module window.

Sub Search_Sheet_Name_in_Closed_Workbook()
Dim xWkb As Workbook
Dim xSht As Worksheet
Dim xShtName As String
xShtName = InputBox("Enter the Sheet Name:")
Application.ScreenUpdating = False
Set xWkb = Workbooks.Open _
("D:\Excel Closed Workbook\Hyperlink PDF files.xlsx")
For Each xSht In xWkb.Worksheets
If xSht.Name = xShtName Then
xWkb.Close SaveChanges:=True
MsgBox "Sheet '" & xShtName & "' has been found!"
Exit Sub
End If
Next xSht
Application.ScreenUpdating = False
MsgBox "Sheet '" & xShtName & "' could not be found!"
End Sub

The Workbooks.Open() command opens the closed workbook. Workbooks.Open (“D:\Excel Closed Workbook\Hyperlink PDF files.xlsx”) is the address of the closed sheet.

Press Ctrl + S to save the code.
Close the Visual Basic window.
Select Macros in the Developer tab. The Macro window will be displayed.