Hafiz Islam
If you have a lot of worksheets in an Excel workbook and you are navigating google for a quick way to find a specific sheet or all sheet names, then you are in a right place! In this article, we will discuss 2 effective methods to search by sheet name in Excel workbook with proper illustrations.
Download Practice Workbook
You can download the following Excel workbook for your practice.
2 Effective Methods to Search Sheet Name in an Excel Workbook
In this section, we will learn 2 methods to search worksheet names in an Excel workbook with proper illustrations and explanations.
You will find the Navigation button of your Excel workbook just above the Status bar.
In an excel workbook containing many sheet names, you can quickly find the sheet you want using this button. Just execute the following steps.
Steps:
- Do right-click on the Navigation button of your excel workbook.
You will see a dialog box that contains all sheet names in your excel workbook.
- Now choose the specific sheet that you need, and finally press OK.
This will navigate you to the selected sheet.
Read More: How to Get Excel Sheet Name (2 Methods)
2. Use VBA Code to Search Sheet Name in an Excel Workbook
You can easily find sheet names by utilizing certain VBA macros. Here we will see how to do that.
Here, the VBA code will list all the sheet names in the Excel workbook. In this section, we will learn how we can get the list of all the sheet names in the Excel workbook by using VBA code. For this, you just have to follow the steps below.
Steps:
- First, go to the Developer tab.
- Then click on the Visual Basic option from the Developer tab and a VBA window pops out.
Or, If you press Alt+F11 keys together, a VBA window will pop out.
- Then click on the Insert tab of the menu bar of the VBA window.
- Then click on Module.
This will open a new Module window.
- Now, copy the following VBA code and paste it into the Module window.
Sub List_SheetNames()
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub - Click on Run of the menu bar or press F5 to execute the VBA code.
This will create a list of all worksheet names in your current sheet.
- Now for jumping to your required sheet, you can assign a hyperlink to these sheet names. Continue to follow the steps to add a hyperlink to each one of the sheets.
- Right-click on your opted sheet name.
- Go to the Link option > Insert Link.
- Select Place in this Document.
- Select your specific sheet.
- Press OK.
Now if you click on the links created, it will take you to the corresponding worksheet.
Read More: How to List Sheet Name in Excel (5 Methods + VBA)
2.2 Search by Typing the Sheet Name in the Input Box
This VBA code will provide you with a search box using the InputBox function. You have to type the sheet name in the search box and the Code will take you to your desired sheet! The steps are simple as the following.
Steps:
- First of all, go to the Developer tab. Then click on the Visual Basic button.
- Then go to the insert tab and select Module. It will open a new Module window in which you have to paste the following VBA code.
If you don’t have the Developer tab in your Excel application, enable it, or press Alt+F11. This will directly open a new module window.
- Now copy the following VBA code and paste it into the module.
Sub Search_SheetName()
Dim Name As String
Dim Found As Boolean
Name = InputBox("Enter sheet name:", "Sheet search")
If Name = "" Then Exit Sub
On Error Resume Next
ActiveWorkbook.Sheets(Name).Select
Found = (Err = 0)
On Error GoTo 0
If Found Then
MsgBox "Sheet '" & Name & "' found and selected!"
Else
MsgBox "The sheet '" & Name & "' not found!"
End If
End Sub
- Then, by clicking the Run button on the menu bar of the Visual Basic tab or pressing F5, a sheet search box will pop up.
- Write down the sheet name that you need to find, and click OK.
- Another dialog box will pop up and it will tell you whether the sheet has been found or not. You need to close it.
Note:
This VBA code needs an exactly matched sheet name.
Read More: How to Search Sheet Name with VBA in Excel (3 Examples)
Conclusion
This article discussed how to search sheet names in an Excel workbook with and without VBA codes. I hope you found this tutorial helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Apply Sheet Name Code in Footer in Excel (3 Ways)
- Select Sheet by Variable Name with VBA in Excel (2 Ways)
- How to Use Sheet Name Code in Excel (4 Applications)
- Rename Sheet in Excel (6 Easy and Quick Methods)
- How to Rename Sheet with VBA in Excel (Both Single and Multiple Sheets)
- Use Excel Sheet Name From Cell Value (Three Ways)
