Search for worksheet; need code for partial name

[moxiepilot]

So I was able to find VBA code to be able to search for a Worksheet within an open workbook. The issue is that there are going to be a lot of sheets, and remembering the exact name is going to be a challenge. Is there a way to add additional code that searches for a partial match and opens that worksheet?

The code I have so far is:

Sub FindSheet()
Dim xName As String
Dim xFound As Boolean
xName = InputBox("Enter sheet name to find in workbook:", "Sheet search")
If xName = "" Then Exit Sub
On Error Resume Next
ActiveWorkbook.Sheets(xName).Select
xFound = (Err = 0)
On Error GoTo 0
If xFound Then
MsgBox "Sheet '" & xName & "' has been found and selected!"
Else
MsgBox "The sheet '" & xName & "' could not be found in this workbook!"
End If
End Sub

Thanks for the help!

 

[mumps]

Try:

Sub FindSheet()
    Dim xName As String
    Dim xFound As Boolean
    Dim ws As Worksheet
    xName = InputBox("Enter sheet name to find in workbook:", "Sheet search")
    If xName = "" Then Exit Sub
    For Each ws In Sheets
        If ws.Name Like "*" & xName & "*" Then
            ws.Select
            MsgBox "Sheet '" & xName & "' has been found and selected!"
            Exit Sub
        End If
    Next ws
    MsgBox "The sheet '" & xName & "' could not be found in this workbook!"
End Sub

 

[moxiepilot]

Thank you. Do you also know how to change the Macro name so that it does not have the workbook title in it? I would like to change the macro to "FindSheet" or something like that rather than "Really long workbook name - Findsheet"

 

[Rick Rothstein]

So I was able to find VBA code to be able to search for a Worksheet within an open workbook. The issue is that there are going to be a lot of sheets, and remembering the exact name is going to be a challenge.

Maybe you can make use of code I posted in this article (entitled 'A Neat "Go To Sheet" Selector') that I posted in my mini-blog here (full instructions included in the article)...

http://www.excelfox.com/forum/showthread.php/1830-A-Neat-quot-Go-To-Sheet-quot-Selector

 

[mumps]

I'm not really sure what you mean. The macro's current name is "FindSheet".

 

[moxiepilot]

I'm not really sure what you mean. The macro's current name is "FindSheet".

If the name search is not exact, no result is generated. So, if I have spreadsheets named Timber, PineTimber, and MapleTimber, if I try to search for "Maple" no sheet is found. So, I'm additionally looking to tag on a part of the code which accounts for partial name searches. I have found some Google results what have "Like." commands in them but since I don't know VBA it is difficult to figure out the right syntax.

 

[Rick Rothstein]

If the name search is not exact, no result is generated. So, if I have spreadsheets named Timber, PineTimber, and MapleTimber, if I try to search for "Maple" no sheet is found. So, I'm additionally looking to tag on a part of the code which accounts for partial name searches. I have found some Google results what have "Like." commands in them but since I don't know VBA it is difficult to figure out the right syntax.

If you downloaded the "A Neat 'Go To Sheet' Selector" example that I posted (see Message #4 ) in mini-blog article located here...

http://www.excelfox.com/forum/showthread.php/1830-A-Neat-quot-Go-To-Sheet-quot-Selector

then if you press Alt+F11 to go into the VB editor and locate "GoToSheetSelectorUserForm" item in the "Project-VBAProject" window list on the left, double-click it and then double click the TextBox (top white box on the form that opened up), and replace the line of code between the For and Next lines of code with this line of code...

[table="width: 500"]
[tr]
	[td]    If LCase(Sheets(X).Name) Like "*" & LCase(TextBox1.Text) & "*" Then ListBox1.AddItem Sheets(X).Name[/td]
[/tr]
[/table]

then when you press CTRL+G, the Listbox will show worksheets whose name's partially match what you have typed.

 

[mumps]

I've made a slight change to the macro I suggested in Post #2 to make the search case insensitive. It should do exactly what you described in Post #6 .

Option Compare Text
Sub FindSheet()
    Dim xName As String
    Dim xFound As Boolean
    Dim ws As Worksheet
    xName = InputBox("Enter sheet name to find in workbook:", "Sheet search")
    If xName = "" Then Exit Sub
    For Each ws In Sheets
        If ws.Name Like "*" & xName & "*" Then
            ws.Select
            MsgBox "Sheet '" & xName & "' has been found and selected!"
            Exit Sub
        End If
    Next ws
    MsgBox "The sheet '" & xName & "' could not be found in this workbook!"
End Sub

You should keep in mind that since only one sheet can be selected at any one time, this macro will select the first sheet with your search criteria. For example, using Timber, PineTimber, and MapleTimber as sheet names, if you enter "Timber" as the search criteria, the first sheet will be selected. IF you enter "Maple" or "maple", MapleTimber will be selected.