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.
Step 4: Click Developer->Macros to run Macro. Select ‘SearchWorksheet’ and click Run.
Step 5: Search Worksheet dialog pops up. Enter worksheet name into the text box, then click OK. For example, enter Sheet2 which really exists.
Step 6: Verify that below message pops up. And Sheet2 is selected directly. User can do operations on Sheet2 directly.
Step 7: Repeat step#4, this time enter a worksheet doesn’t exist. Verify that below message pops up.
