Macro to copy and paste

[Bryan]

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.

 

[Tom Ogilvy]

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.

 

[Bryan]

Thanks Tom. I tried the code.

After running the macro the word "student" appeared in
cell A4 but the entire row from sheet1 was not copied.

Answers to your questions: There would be more than one
instance of "student" on sheet1; I don't know if I would
need to mark the rows or not however I would not want
duplicates. Basically once it has searched all rows on
Sheet1 I would want the search to end.

Any ideas?

Appreciate your help,
Bryan

 

[Tom Ogilvy]

change rng.copy Destination:=rng1 to
rng.EntireRow.copy Destination:=rng1

See the excel vba help on the FindNext method (code example) to see an
example of how to find multiple instances of student.