Macro to copy and paste
 

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.

Macro to copy and paste
 

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.

Macro to copy and paste
 

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


-----Original Message-----
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.


.

Macro to copy and paste
 

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.

--
Regards,
Tom Ogilvy



"Bryan" wrote in message
...
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


-----Original Message-----
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.


.