Yet another Mod to this code recuested
This is the code from Page three
Instead of displaying the results how could it be modified to copy the results of the search and paste to a separate sheet.
or
Copy the search word to the next column beside the row where the words was found
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc 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
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")
If myFind = 2 Then Exit Sub
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"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub
