VBA - find sheet name

[XLML]

Good morning. I have to search through about a hundred sheet names to find an employee name. Each sheet is renamed for the employee name (ie Smith). Is there a macro that will prompt for the name and search thru the entire workbook for it?

PS - since there are other characters in some names (hyphens, apostrophes etc) I would like to include DIM xxxx as STRING.

XLML

 

[TommyGun]

Why not select all of your sheets, and then do a Find command in XL? This will search each sheet for whatever you want.

 

[XLML]

I should clarify that I need to search only by sheet name (ie Sheet1) and not the data within a sheet. If I rename Sheet2 as "test" and do your suggestion, it doesn't highlight the sheet. Anyway thru VBA?
XLML

 

[TommyGun]

Okay, then try this. It will activate the sheet if found.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetSheet()

<SPAN style="color:#00007F">Dim</SPAN> SearchData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

SearchData = InputBox("Enter the customer name.")

<SPAN style="color:#00007F">If</SPAN> SearchData <> vbNullString <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Sheets(SearchData).Activate
    <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN> MsgBox "Unable to find sheet named: " & SearchData
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Richie(UK)]

Hi X,

Is this what yoh had in mind?

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        MsgBox "You cancelled!"
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub

Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

HTH

EDIT : Oops, sorry Tommy - didn't notice your post :wink:

 

[XLML]

Perfect.... thank you for your responses.
XLML

 

[erl11]

Hi Guys, thanks for the code.. for my needs i also wanted to find the sheet if it at least contained part of the search string rather than only an exact match.. in case anyone else is looking for the same thing, here are the modifications:

Sub FindWS()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the sheet name to serach for")
    If strWSName = vbNullString Then
        Exit Sub
    End If
    
    If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(s.Name, strWSName) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
    
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

 

[sjfir]

Hi, I have used this code and it worked great I was wondering if there was a way when the sheet is selected that you could have it run a filter and copy from another sheet. For example if i use this code to search for GTA and it selects that sheet I have GTA in Cell A1 but I have another sheet caled data that has all the records for GTA that I'd like copied from data to GTA. is this possible?