dim rng as range, rng1 as range
With worksheets("Sheet1")
set rng = .cells.find(Sheet2.Range("A1"),After:=.Range("A1") )
end with
if rng.Address < "$A$1" then
set rng1 = worksheets("Sheet2").Cells(rows.count,1).End(xlup)
if rng1.row < 4 then
set rng1 = Worksheets("Sheet2").Cells(4,1)
else
set rng1 = rng.offset(1,0)
end if
rng.copy Destination:=rng1
End if


would be a basic approach.
You haven't said whether there will be multiple instances of student in
sheet1 or if you need to mark rows already copied to avoid duplicates.

--
Regards,
Tom Ogilvy

"Bryan" wrote in message
...
I am looking for a macro code that will search for cell
reference and copy and paste the row to a different
worksheet.

For example, if "student" is typed into cell A1 of Sheet2
I would like Excel to search Sheet1 for "student".
If "student" is found in Sheet1 I would like the entire
row that "student" is found in to be copied to row D in
Sheet2. Of course each subsequent time "student" is found
it should be copied to the next row in Sheet2 (D, E, F,
etc).

Any help would be greatly appreciated.