4

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 4

9

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

3

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
  • needless use of a loop and Select.
    – brettdj
    Apr 16, 2014 at 11:17
  • @brettdj That is true. I have no explanation to why I did it like that! I have updated the code. Thanks :)
    – Netloh
    Apr 16, 2014 at 11:23
0

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.

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .