The below code copies over a row from Sheet1 that contains a certain criteria (input via msgbox) to Sheet2. Every time it's run, it will stack these rows into Sheet2, one after the other, without overwriting anything.
I love this macro. It works perfectly for me...except for one problem. It only copies over one instance of each match. I would like it to copy over all instances - multiple rows if they exist. How can I do this?
I would prefer not start over with a new code unless necessary.
Thanks!
Sub FindAndCopyEmailAddress()
Dim vnt_Input As Variant
Dim rng_Found As Excel.Range
Dim wks1 As Excel.Worksheet, wks2 As Excel.Worksheet
Dim rng_target As Excel.Range
Dim l_FreeRow As Long
On Error Resume Next
Set wks1 = ThisWorkbook.Worksheets("Sheet1")
Set wks2 = ThisWorkbook.Worksheets("Sheet2")
On Error GoTo ErrorHandler
If wks1 Is Nothing Or wks2 Is Nothing Then
Err.Raise vbObjectError + 20000, , "Cannot find sheet1 or 2"
End If
vnt_Input = InputBox("Please enter the address that you're looking for", "Address Copier")
If vnt_Input = "" Then GoTo ExitPoint
Set rng_Found = wks1.Cells.Find(What:=vnt_Input, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng_Found Is Nothing Then
MsgBox "Cannot find that address."
GoTo ExitPoint
End If
l_FreeRow = wks2.UsedRange.Row + wks2.UsedRange.Rows.Count
If l_FreeRow > wks2.Rows.Count Then
Err.Raise vbObjectError + 20000, , "No free rows on sheet " & wks2.Name
End If
Set rng_target = wks2.Cells(l_FreeRow, 1)
rng_Found.EntireRow.Copy rng_target
ExitPoint:
On Error Resume Next
Set rng_Found = Nothing
Set wks1 = Nothing
Set wks2 = Nothing
Set rng_target = Nothing
On Error GoTo 0
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitPoint
End Sub
Bookmarks