How to search for multiple occurrences of data in multiple workbooks with multiple worksheets automatically using a macro. Watch the video below before studying the macro code:
Option Explicit
Sub SearchAllFolders()
Dim strSearch As String
Dim strPath As String
Dim strFile As String
Dim wksReport As Worksheet
Dim wbk As Workbook
Dim wks As Worksheet
Dim startROW As Long
Dim rngFound As Range
Dim strFirstAddress As String
‘Makes our macro code run faster
Application.ScreenUpdating = False
‘Path to our folder which contains the workbooks
strPath = “C:\MyExcelData”
‘Ask the user for the search term and offer a method also to cancel the search
strSearch = Application.InputBox(“Enter the text to search”, “My choice of text”, “Enter Search Text”, vbOKCancel)
If strSearch = “False” Then Exit Sub
‘Add a new worksheet
Set wksReport = Worksheets.Add
startROW = 1
With wksReport
‘Add the headers for the extracted data
.Cells(startROW, 1) = “Workbook”
.Cells(startROW, 2) = “Worksheet”
.Cells(startROW, 3) = “Cell Address”
.Cells(startROW, 4) = “Text Found”
'Loop through all the files in the folder
strFile = Dir(strPath & "\*.xls*")
Do While strFile <> ""
'Check for each new file
Set wbk = Workbooks.Open(Filename:=strPath & "\" & strFile)
'Loop through each worksheet in the workbook
For Each wks In wbk.Worksheets
Set rngFound = wks.UsedRange.Find(strSearch)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
End If
Do
'Extract multiple occurrences of the same data
If rngFound Is Nothing Then
Exit Do
Else
startROW = startROW + 1
.Cells(startROW, 1) = wbk.Name
.Cells(startROW, 2) = wks.Name
.Cells(startROW, 3) = rngFound.Address
.Cells(startROW, 4) = rngFound.Value
End If
Set rngFound = wks.Cells.FindNext(after:=rngFound)
Loop While strFirstAddress <> rngFound.Address
Next
'Close the opened workbook without saving it
wbk.Close (False)
strFile = Dir
Loop
'Make all data in the cells readable
.Columns("A:D").EntireColumn.AutoFit
End With
‘Delete the headers and the worksheet if no data found
If wksReport.Cells(2, 1) = “” Then
MsgBox “All Excel files in folder searched!” & vbCrLf & “No data found!”
Cells(startROW, 1) = “”
Cells(startROW, 2) = “”
Cells(startROW, 3) = “”
Cells(startROW, 4) = “”
On Error Resume Next
Application.DisplayAlerts = False
wksReport.Delete
Else
MsgBox “All Excel files in folder searched.” & vbCrLf & “Data extracted.”
End If
‘Clean up the memory
Set wksReport = Nothing
Set wks = Nothing
Set wbk = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Download a sample file

Further Reading: