Lookup Macro Trouble

J

JennF

Can you create a macro that will lookup a text string within the
spreadsheet, and if true copy that entire row to another spreadsheet
within the workbook?
 
N

Nick Hodge

Jenn

The following is some starter code. It uses my Sheet1 (lookup sheet),
Sheet2 (Copy sheet). It will place the found row below the last one in the
copy sheet. (Presuming row 1 is headers)

It does no error checking past the searched item is found

Sub findAndMove()
Dim sData As String
Dim rFindRange As Range
Dim lLastRow As Long
sData = InputBox("Enter search string")
If Worksheets("Sheet1").Cells.Find(sData, , , xlWhole) Is Nothing Then
MsgBox "Search string not found", vbOKOnly
Exit Sub
End If
lLastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row + 1
Set rFindRange = Worksheets("Sheet1").Cells.Find(sData, , , xlWhole)
rFindRange.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" &
lLastRow)
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top