Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste using macro | Excel Discussion (Misc queries) | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
Copy Paste in Macro | Excel Programming | |||
Macro Won't Copy/Paste | Excel Programming |