How to search for text across multiple worksheets and copy rows?

I would like to search for text across multiple worksheets and summarize the entire row in one worksheet. In my example, I am looking to find all instances where the words "change order" appears, and then copy the entire row into the "Summary" tab so that I can sum the change orders. The attached example is a simplified version, and the actual spreadsheet contains at least 50 different worksheets with various titles.

Any help would be greatly appreciated!

Like this?

Sub x()
  
Dim rFind As Range, sFind As String, sAddr As String, ws As Worksheet
 
sFind = "Change Order"

For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        With ws.UsedRange
            Set rFind = .Find(What:=sFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    rFind.EntireRow.Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)
                    Set rFind = .FindNext(rFind)
                Loop While rFind.Address <> sAddr
                sAddr = ""
            End If
        End With
    End If
Next ws
     
End Sub

That worked like it should have, but I guess my example was too simplistic because it didnt work in the actual spreadsheet I wanted it to. Here is an exerpt of the actual spreadsheet I am dealing with. To make things more interesting, is there a way to put out to the side of each change order the worksheet that it came from?

Thank you for your help!

Try this:

Sub x()
  
Dim rFind As Range, sFind As String, sAddr As String, ws As Worksheet
 
sFind = "Change Order"

For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        With ws.UsedRange
            Set rFind = .Find(What:=sFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    ws.Cells(rFind.Row, 2).Resize(, 8).Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)
                    Sheets("Summary").Range("I" & Rows.Count).End(xlUp)(2) = ws.Name
                    Set rFind = .FindNext(rFind)
                Loop While rFind.Address <> sAddr
                sAddr = ""
            End If
        End With
    End If
Next ws
     
End Sub

Wow that worked like a charm! You probably just saved me hours of tedious work. Thank you!

Actually this helped too just had to modify and do some research about the script. Though I am not a pro.

I am looking at doing something quite similar to what the initial question was asking. The macro provided works for what I am trying to do, with two exceptions:

First; instead of looking for "change order", I am looking for either "Low Outlier" or "High Outlier". These two will be the result of the following formula:

=IF(O21="","",IF(O21<VLOOKUP("Low Outlier Cost",A:B,2,FALSE),"Low Outlier",IF(O21>VLOOKUP("High Outlier Cost",A:B,2,FALSE),"High Outlier","")))

The macro does not seem to recognize the result of this formula. If I copy the result and paste it as a value, however, then the macro recognizes it and performs the appropriate functions. Do I have to copy and paste all the results of above formula as values for this to work or is there a way around it? I have almost 100 worksheets in the document so it would be quite a mess to have to go through the whole thing.

Second; when the macro copies the various fields to the summary page, it copies the forumlas and not values. Therefore the results show up as #REF instead of the values of the cells. How do I change the macro to only pasting values and not formulas?

arlu1201,

Sorry about that.