How to Find Specific Sheet Name in Excel

I have a excel file with 55 sheets in it

It is very tedious to find a specific sheet name in the excel file since it has 55 sheets in it

I have used the search function to find specific sheet name but it is not searching the sheet name

Is there a way to search a Sheet name in Excel

4 Answers

In 2013... RIGHT CLICK on the Navigation area in the STATUS BAR (far left area with buttons) and the sheet names will appear and you can choose whichever you want to activate. Joan B

The build in search function in Excel does not allow you to search in the names of the sheets. You could instead include the code bellow as a macro in either the actual workbook or as a Add-In to your Excel installation.

When this code is called it will prompt you for a search name and try to find and select the sheet with that name.

Sub SearchSheetName() Dim sName As String Dim sFound As Boolean sName = InputBox(prompt:="Enter sheet name to find in workbook:", Title:="Sheet search") If sName = "" Then Exit Sub sFound = False On Error Resume Next ActiveWorkbook.Sheets(sName).Select If Err = 0 Then sFound = True On Error GoTo 0 If sFound = False Then MsgBox prompt:="The sheet '" & sName & "' could not be found in this workbook!", Buttons:=vbExclamation, Title:="Search result" End If
End Sub

2

You can use VBA to create a list of all of the worksheets in your workbook automatically.

If you insert this code into the VB panel for your workbook, then go back to your workbook and make a blank sheet and run this as a macro:

Sub SheetNames() Columns(1).Insert For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next i
End Sub

This should then create a full list of all of your worksheets. You could also then assign a hyperlink to each one to jump to that sheet easily if you wanted.

Quickest to run a simple test as below

Sub Tested()
Dim strTest As String
strTest = "Your Sheet Name"
MsgBox strTest & "exists:= " & SheetExists(strTest)
End Sub
Function SheetExists(ByVal strTest As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(strTest)
SheetExists = (Not ws Is Nothing)
End Function