How to Search by Sheet Name in Excel Workbook (2 Handy Methods)

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.

Table of Contents hide

Download Practice Workbook

2 Effective Methods to Search Sheet Name in an Excel Workbook

1. Right-Click on the Navigation Button to Find Sheet Name

2. Use VBA Code to Search Sheet Name in an Excel Workbook

2.1 Search with VBA and Navigate from Sheet Name List

2.2 Search by Typing the Sheet Name in the Input Box

Conclusion

Download Practice Workbook

You can download the following Excel workbook for your practice.

Search Sheet Name.xlsm

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.

1. Right-Click on the Navigation Button to Find Sheet Name

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.

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.

2.1 Search with VBA and Navigate from Sheet Name List

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.

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.

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.