How to Search Sheet Name with VBA in Excel (3 Examples)

In this article, we will learn to search the sheet name with VBA in Excel. VBA stands for Visual Basic for Applications. Sometimes, our excel sheet contains a large number of sheets and we need to search for a specific sheet. It is very time-consuming if we try to search the sheet name manually. So, today, we will use VBA to search the sheet name in excel. In this article, we are going to demonstrate 3 important VBA examples.

Table of Contents hide

Download Practice Book

3 Examples to Search Sheet Name with VBA in Excel

1. Excel VBA to Search Sheet Name and Display Search Result

2. Search Sheet Name and Select it with VBA in Excel

3. Excel VBA to Search Sheet Name in Closed Workbook and Display Search Result

Things to Remember

Conclusion

Download Practice Book

Download the practice book here.

Search Sheet Name with VBA.xlsm

3 Examples to Search Sheet Name with VBA in Excel

To explain these examples, we will use a dataset that contains information about the Department and the Age of some employees.

1. Excel VBA to Search Sheet Name and Display Search Result

In the first example, we will use VBA to search the sheet name ‘Display Result’ in an open excel workbook. The workbook contains many other different sheets along with the desired one. In this case, we will search the sheet name and display the result in a message box.

Let’s follow the steps below to learn this example.

STEPS:

In the first place, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.
Alternatively, you can use the keyboard shortcut for it. Press Alt + F11 to open the Visual Basic window.

Secondly, select Insert and then, Module from the drop-down menu in the Visual Basic window. This will open the Module window.

Thirdly, type 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

Here, we have declared ShtName and ShtSearch as variables. ShtName denotes the sheet name and ShtSearch searches the sheet name. We have used the On Error Resume Next statement to run the next statements instantly after the statement that generated the error and the On Error GoTo 0 statement to disable any enabled error.

After that, press Ctrl + S to save the code.
Then, close the Visual Basic window.
At this time, select Macros from the Developer tab. The Macro window will appear.

Now, select the code and Run it from the Macro window.

After running the code, a message box will ask for the sheet name.
Write the sheet name and click OK to proceed.

Finally, another message box will display the search result like the picture below.

2. Search Sheet Name and Select it with VBA in Excel

In this example, we will search the sheet name and select it with Excel VBA. Here, we will not show the result in a message box. Rather, we will select the searched sheet. Let’s pay attention to the steps below.

STEPS:

To begin with, select Visual Basic from the Developer tab. This will open the Visual Basic window. You can also press Alt + F11 to open it.

In the second step, select Insert and then, select Module from the drop-down menu. The Module window will appear.

After that, type 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

Here, we have created the ShtSearch function to check the existence of the searched sheet in an open workbook. We have used the ShtName = vbNullString command to show ‘Cancelled!’ if we close the message box.

Next, press Ctrl + S to save the code and then, close the Visual Basic window.
Now, go to the Developer tab and select Macros to open the Macro window.

Then, select the desired code and Run it from the Macro window.

After running the code, you have to write the sheet name in the message and click OK.

Finally, if it finds the sheet in the open workbook, it will select the sheet.

Otherwise, it will display the ‘Sheet name could not be found!’ message.

Read More: How to Get Excel Sheet Name (2 Methods)

3. Excel VBA to Search Sheet Name in Closed Workbook and Display Search Result

In the last example, we will search for a sheet name in a closed workbook with VBA. Here, we will use a workbook that contains a sheet named ‘Insert Tab’. We will search for this sheet name and display the result in a message box.

Let’s observe the steps below to learn more about this example.

STEPS:

First of all, go to the Developer tab and select Visual Basic. This will open the Visual Basic window.
Alternatively, you can press Alt + F11 to open the Visual Basic window.

After that, select Insert and then, Module from the drop-down menu to open the Module window.

Then, type the VBA 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

Here, we have used the Workbooks.Open() command to open the closed workbook. Workbooks.Open (“D:\Excel Closed Workbook\Hyperlink PDF files.xlsx”) denotes the address location of the closed sheet. It means the VBA code will have to open the file located on the D drive inside the Excel Closed Workbook file and the name of the file is Hyperlink PDF files. We will then have to write the extension of the file. As it is an excel workbook, we have written .xlsx.

Press Ctrl + S to save the code and close the Visual Basic window.
Now, go to the Developer tab and select Macros. It will open the Macro window.

Select the desired code from the Macro window and Run it.

After that, a message box will appear. Type the name of the sheet you want to search in the closed workbook and click OK.

Finally, you will see a message like the below picture if the searched name exists in the closed workbook.

Things to Remember

There are certain things we need to remember to search the sheet name with VBA in Excel.

In the above methods, we need to write the sheet name fully in the message box. For example, if you write Display instead of Display Result, it will show ‘Sheet ‘Display’ could not be found!’.
You can press Alt + F11 to open the Visual Basic window.
Again, you can press the F5 key to run the code instead of running it from the Macro window.

Conclusion

We have demonstrated 3 easy examples of Search a Sheet Name in Excel with VBA. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.