How to Search and Locate Worksheet in Excel

,

If there are multiple worksheets exist in excel, there will be a lot of worksheet tabs in tab bar. And it is difficult to find a worksheet we want. Though we can search worksheet by right click in worksheet bar to activate worksheet, a simple and convenient way will be better especially there are a lot of worksheets exist. This article will provide you a simple way to solve this problem by VBA.

Search and Locate Worksheet by VBA


Suppose there are multiple worksheets in this workbook. Worksheet names are squeezed in the tab bar. We want to find out a worksheet and open this worksheet quickly. We can edit VBA script to achieve our thought.

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step 2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1:

Sub SearchWorksheet()

Dim ShName As String

Dim ShFound As Boolean

ShName = InputBox("Enter worksheet name to find in this workbook:", "Search Worksheet")

If ShName = "" Then Exit Sub

On Error Resume Next

ActiveWorkbook.Sheets(ShName).Select

ShFound = (Err = 0)

On Error GoTo 0

If ShFound Then

MsgBox "Worksheet '" & ShName & "' has been found!"

Else

MsgBox "Worksheet '" & ShName & "' could not be found!"

End If

End Sub

Comment:

You can change the message in message box per your demands.

Step 3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Search and Locate Worksheet 1

Step 4: Click Developer->Macros to run Macro. Select ‘SearchWorksheet’ and click Run.

Search and Locate Worksheet 2

Step 5: Search Worksheet dialog pops up. Enter worksheet name into the text box, then click OK. For example, enter Sheet2 which really exists.

Search and Locate Worksheet 3

Step 6: Verify that below message pops up. And Sheet2 is selected directly. User can do operations on Sheet2 directly.

Search and Locate Worksheet 4

Step 7: Repeat step#4, this time enter a worksheet doesn’t exist. Verify that below message pops up.

Search and Locate Worksheet 5