Search multiple worksheets and list results

[mike0123m]

I have an excel file with multiple worksheets, each sheet for each of the past five years. On each worksheet, there is a column for the job number, a column for the job description, and a column with a short description of what the job was. I would like to have a text box pop up (probably just by pushing a button on the worksheet), enter a few key words in the textbox, and then have excel search through each worksheet and list on a separate sheet (or on a userform) the job number, job name, and description where the key words were found. This is a little over my head. Thanks!

 

[pboltonchina]

Hi, I use the code below for the same function you want, and it works for me.

Private Function SheetExists(SheetName As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(SheetName)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function
Sub FindAllSheets()
    Dim Found As Range, ws As Worksheet, LookFor As Variant
        LookFor = InputBox("Enter value to find")
            
            If LookFor = "" Then Exit Sub
            
            '   Clear or Add a Results sheet
            If SheetExists("Search Results") Then
              Sheets("Search Results").Activate
              Range("A2").Select
              Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
              Selection.ClearContents
            Else
                Sheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Search Results"
            End If
            
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> "Search Results" Then
                     Set Found = ws.Cells.Find(What:=LookFor)
                     If Found Is Nothing Then
                         Range("D5").Select
                     Else
                         Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                     End If
                End If
            Next ws
End Sub

 

[mike0123m]

Thanks, pboltonchina. That did the trick!