'<< We're using "FileSearch" to open all the     >>
 '<< books in a folder - as an example of what  >>
 '<< this can be used for, we'll then search thru >>
 '<< all the sheets in all the books in the folder  >>
Option Explicit 
 
Sub SearchAllBooksInFolder() 
    Dim Cell As Range, FirstAddress$, i%, N% 
    Dim LookingFor$, ThisBook As Workbook 
     
    Set ThisBook = ThisWorkbook 
    LookingFor = InputBox("What do you want to find?", "Find What?") 
    If LookingFor = Empty Then Exit Sub 
     
     'clear columns 1 to 4 ready to receive new search results
    Sheets(1).Range(Columns(1), Columns(4)).ClearContents 
    Application.ScreenUpdating = False 
     
     'search all the sheets in ThisBook first
    For i = 1 To Sheets.Count 
        With Sheets(i).Range("A1:D500") 
            Set Cell = .Find(LookingFor, LookIn:=xlValues, searchorder:=xlByRows, _ 
            LookAt:=xlPart, MatchCase:=False) 
            If Cell Is Nothing Then '<< there's nothing on this sheet
                GoTo Finish1 
            Else 
                FirstAddress = Cell.Address '<< (bookmark)
                Do 
                     'add this item to the search results
                    With ThisBook.Sheets(1) 
                        Range("A65536").End(xlUp).Offset(1, 0) = ActiveWorkbook.Name 
                        Range("B65536").End(xlUp).Offset(1, 0) = Sheets(i).Name 
                        Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address 
                        Range("D65536").End(xlUp).Offset(1, 0) = "(" & Cell.Value & ")" 
                    End With 
                     'look for any others
                    Set Cell = .FindNext(Cell) 
                Loop Until Cell Is Nothing Or Cell.Address = FirstAddress 
            End If 
        End With 
Finish1: 
    Next i 
     
     'now open & search all the other books in the folder
    With Application.FileSearch 
        .LookIn = ActiveWorkbook.path 
        .Filename = "*.xls" '<< only search workbooks
        If .Execute > 0 Then 
            For N = 1 To .FoundFiles.Count 
                If .FoundFiles(N) <> ThisWorkbook.FullName Then 
                    Application.Workbooks.Open(.FoundFiles(N)).Activate 
                     
                     'search all the sheets in the current book
                    For i = 1 To Sheets.Count 
                        With Sheets(i).Range("A1:D500") 
                            Set Cell = .Find(LookingFor, LookIn:=xlValues, searchorder:=xlByRows, _ 
                            LookAt:=xlPart, MatchCase:=False) 
                            If Cell Is Nothing Then '<< there's nothing on this sheet
                                GoTo Finish2 
                            Else 
                                FirstAddress = Cell.Address '<< (bookmark)
                                Do 
                                     'add this item to the search results
                                    ThisBook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = ActiveWorkbook.Name 
                                    ThisBook.Sheets(1).Range("B65536").End(xlUp).Offset(1, 0) = Sheets(i).Name 
                                    ThisBook.Sheets(1).Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address 
                                    ThisBook.Sheets(1).Range("D65536").End(xlUp).Offset(1, 0) = "(" & Cell.Value & ")" 
                                     'look for any others
                                    Set Cell = .FindNext(Cell) 
                                Loop Until Cell Is Nothing Or Cell.Address = FirstAddress 
                            End If 
                        End With 
Finish2: 
                    Next i 
                     
                    ActiveWorkbook.Close savechanges:=False 
                End If 
                 'search finished in current book, search the next book
            Next N 
        End If 
    End With 
     
     'go back to "ThisBook"
    Sheets(1).Activate 
    Range("B1") = "Search results for " & "''" & LookingFor & "''" 
End Sub