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

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!