[Excel VBA] How to Batch Search All Sheets in All Open Workbooks

Do you ever need to find where a specific keyword or person’s name is used across multiple Excel files at once? Using Excel’s standard search function requires opening books one by one and repeating the search, which is very time-consuming.

With VBA, you can create a powerful “cross-search” tool that searches for a text string across all currently open workbooks and all their sheets, listing the results in a single sheet.

In this article, I will explain the practical code using the .Find and .FindNext methods to achieve this.

Preparation: The Result Output Sheet

This macro writes the search results to a specific sheet. In the workbook where you write the macro, create a sheet named “SearchResults” in advance and set up the following headers in the first row:

Column A	Column B	Column C	Column D
Workbook Name	Sheet Name	Count	Cell Address

The Completed VBA Code

Sub SearchAllOpenWorkbooks()

    ' Declare variables
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim searchTerm As String
    Dim resultsSheet As Worksheet
    Dim outputRow As Range
    Dim foundCell As Range
    Dim firstFoundAddress As String

    ' --- Settings ---
    searchTerm = "Tokyo Branch" ' String to search for
    Set resultsSheet = ThisWorkbook.Worksheets("SearchResults") ' Sheet to output results
    ' --- End Settings ---
    
    ' Initialize variables for output
    resultsSheet.Range("A2:D" & Rows.Count).ClearContents
    Set outputRow = resultsSheet.Range("A2")

    ' --- 1. Loop through all open workbooks ---
    For Each wb In Workbooks
        ' --- 2. Loop through all sheets in the workbook ---
        For Each ws In wb.Worksheets
            ' Exclude the results sheet itself from the search
            If ws.Parent.FullName = resultsSheet.Parent.FullName And ws.Name = resultsSheet.Name Then
                ' Do nothing
            Else
                ' --- 3. Search for the first cell using Find method ---
                Set foundCell = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)
                
                ' --- 4. If found, search for the rest using FindNext ---
                If Not foundCell Is Nothing Then
                    firstFoundAddress = foundCell.Address
                    
                    ' Combine found cells into a single Range object using Union
                    Dim foundRange As Range
                    Set foundRange = foundCell
                    
                    Do
                        Set foundCell = ws.Cells.FindNext(After:=foundCell)
                        ' Loop exit condition: No longer found or returned to the first found cell
                        If foundCell Is Nothing Then Exit Do
                        If foundCell.Address = firstFoundAddress Then Exit Do
                        
                        Set foundRange = Union(foundRange, foundCell)
                    Loop
                    
                    ' --- 5. Write search results to the sheet ---
                    outputRow.Cells(1, 1).Value = wb.Name
                    outputRow.Cells(1, 2).Value = ws.Name
                    outputRow.Cells(1, 3).Value = foundRange.Count
                    outputRow.Cells(1, 4).Value = foundRange.Address(False, False)
                    
                    ' Move to the next output row
                    Set outputRow = outputRow.Offset(1)
                End If
            End If
        Next ws
    Next wb
    
    MsgBox "Search complete for all workbooks."

End Sub

Explanation of Key Points

1. & 2. Nested Loops to Iterate Through All Sheets

For Each wb In Workbooks
    For Each ws In wb.Worksheets

The first two For Each loops sequentially process all currently open workbooks (Workbooks collection) and all sheets within those workbooks (Worksheets collection).

3. Search for the First Cell Using .Find

Set foundCell = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)

Use the .Find method to search within a sheet.

• What: Specifies the string to search for.
• LookIn: Specifies whether to search values (xlValues) or formulas (xlFormulas).
• LookAt: Specifies whether to match the whole cell (xlWhole) or part of the cell (xlPart).

The Find method returns the found cell as a Range object. If not found, it returns Nothing.

4. Loop Processing Using .FindNext

The .Find method only returns the first cell found. To find all matching cells on the sheet, you must execute .FindNext repeatedly in a loop. This is a standard pattern in VBA:

1. Store the address of the first found cell in firstFoundAddress.
2. Start a Do...Loop.
3. Search for the next cell using .FindNext.
4. If the address of the found cell matches firstFoundAddress (meaning it has circled back to the start), exit the loop.

5. Consolidating Results with the Union Function

The Union function combines multiple Range objects into a single Range object. Inside the loop, every time FindNext locates a cell, it is added to foundRange using Union. This builds a single Range object containing all found cells in that sheet. This allows you to easily get the total count (.Count) and addresses (.Address).

Summary

Cross-searching all open workbooks and sheets is achieved by combining the following elements:

1. Access all sheets using nested For Each loops.
2. Find the first matching cell using .Find.
3. Find all subsequent matches using .FindNext within a Do…Loop.
4. Group found cells into a single range using the Union function.
5. Write the results to a dedicated sheet.

Based on this powerful search macro, you can apply it to tasks like “Batch Replace” to dramatically improve the efficiency of routine correction work across multiple files.