Discussion Locked

Search for a specific excel worksheet

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How do you search for a specific worksheet assuming that an excel file contains more than 500 worksheet? The find option only searches in rows/columns only. There might be some other way for worksheet. Please advise. Thanks!

joanna

3 replies

by peyison

Hi Joanna,

One way is to use a VBA macro. Paste this code into a VBA code module:

Public Sub SearchForSheet()
Dim sSearchText As String
Dim wsWorkSheet As Worksheet

sSearchText = UCase(InputBox("Enter the name of the worksheet to searchfor:", "Worksheet Search"))

For Each wsWorkSheet In ActiveWorkbook.Worksheets
If InStr(UCase(wsWorkSheet.Name), sSearchText) > 0 Then
wsWorkSheet.Activate
Exit For
End If
Next

End Sub

To paste the code:* Select Tools -> Macros -> Visual Basic Editor

* Press [CTRL]+R to open the Project Window

* From the Project Window, double click on ThisWorkbook (Under Microsoft Excel Objects)

* Click in the right hand window (there should be a drop down list at the top left of the window that says (General)

* Paste the code. The top right drop down should say SearchForSheet.

* Close the VBA editor and return to Excel.

* Choose Tools -> Macro -> Macros

* Select the SearchForSheet Macro and press Options

* Enter a shortcut key

* Press OK and Cancel

You're ready to start using your macro:

* Press [CTRL]+ the key you entered

* Enter the name, or part of the name, of the sheet you want to find.

You can also assign the macro to a button. Let me know if you want info on how to do that.

Hope this helps.

This discussion is closed to new replies.