Search workbook and copy row results to worksheet

smcgrath171 Posts 5 Registration date Monday February 29, 2016 Status Member Last seen March 1, 2016 - Feb 29, 2016 at 06:42 AM
Hi, brand new to the site, but saw multiple posts that seem very close to what I am looking for. I have a workbook with 31 sheets that contain info in A4:K15 that I would like to search the entire workbook and have any row containing the key word to be copy and pasted. I have the following code that searches and produces the number of times and location of the results, but can't figure out how to copy to the "Search" sheet.

Public Sub FindText()

Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws

If ws.Name = "Master" Then GoTo myNext
If ws.Name = "Lists" Then GoTo myNext

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address

foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf

Set Found = .UsedRange.FindNext(Found)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If

End With

Next ws

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"

MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub