search for/next or loop

[Jockster]

Searchinfg for a value in one workbook and copy the entire row to another workbook.

I wish to search for "John L Smith" in sheet 1 column D in workbook Y and, when found, to copy the entire row (values, not formulae) to Workbook Z sheet 1 column A.
Once done to continue to search for a second entry of same value in the same column and to copy to Workbook Z when found and so on until the last found row of data.
Can this be done?

Thanks.

 

[Trevor G]

You need something like this

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Workbooks.Open("M:\Access Files\tblStaff Import.xls").Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub