How to Create Macro to Search, Copy, & Paste?

December 2016




Issue


Here's what I want to do. I have a worksheet with customer data by row and I also have a worksheet with an invoice I want to auto populate with the customer info from data worksheet.

I would like to build a macro that will search for the reference number I tell it and copy the row with the customer data to the invoice worksheet where I will pull each needed field to the invoice (which I'll do on my own).

I built a macro that searchs the reference number, selects the row, and copy and pastes the row into the other worksheet. My problem is when I go to put a new reference number into my little search box I made it still searches the reference number I setup the macro with and references only the inital row I setup the macro withs data :(

I wish I could tell it to:

Search for a reference number, which I copy the reference number and paste it into find on the other worksheet, and then i'm pressing shift space to select and copy the data and paste it into the other worksheet. It keeps referencing the inital ranges and reference number.

Please help.

Here's what I have so far. I'm kind of a noob. All the active scroll at the end is me scrolling and pasting the macro under my invoices so I can pull data later.


Range("AM5:AS5").Select 
ActiveCell.FormulaR1C1 = "33629" 
Sheets("Sheet2").Select 
Cells.Find(What:="33629", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
False, SearchFormat:=False).Activate 
Rows("6:6").Select 
Range("C6").Activate 
Selection.Copy 
Sheets("Sheet1 (2)").Select 
ActiveWindow.SmallScroll Down:=12 
ActiveWindow.ScrollRow = 14 
ActiveWindow.ScrollRow = 15 
ActiveWindow.ScrollRow = 16 
ActiveWindow.ScrollRow = 17 
ActiveWindow.ScrollRow = 18 
ActiveWindow.ScrollRow = 19 
ActiveWindow.ScrollRow = 20 
ActiveWindow.ScrollRow = 21 
ActiveWindow.ScrollRow = 22 
ActiveWindow.ScrollRow = 23 
ActiveWindow.ScrollRow = 24 
ActiveWindow.ScrollRow = 25 
ActiveWindow.ScrollRow = 26 
ActiveWindow.ScrollRow = 27 
ActiveWindow.ScrollRow = 29 
ActiveWindow.ScrollRow = 31 
ActiveWindow.ScrollRow = 32 
ActiveWindow.ScrollRow = 33 
ActiveWindow.ScrollRow = 36 
ActiveWindow.ScrollRow = 38 
ActiveWindow.ScrollRow = 40 
ActiveWindow.ScrollRow = 42 
ActiveWindow.ScrollRow = 45 
ActiveWindow.ScrollRow = 47 
ActiveWindow.ScrollRow = 49 
ActiveWindow.ScrollRow = 51 
ActiveWindow.ScrollRow = 53 
ActiveWindow.ScrollRow = 54 
ActiveWindow.ScrollRow = 55 
ActiveWindow.ScrollRow = 56 
ActiveWindow.ScrollRow = 57 
ActiveWindow.ScrollRow = 58 
ActiveWindow.ScrollRow = 59 
ActiveWindow.ScrollRow = 60 
ActiveWindow.ScrollRow = 61 
ActiveWindow.ScrollRow = 62 
ActiveWindow.ScrollRow = 63 
ActiveWindow.ScrollRow = 64 
ActiveWindow.ScrollRow = 65 
ActiveWindow.ScrollRow = 67 
ActiveWindow.ScrollRow = 68 
ActiveWindow.ScrollRow = 69 
ActiveWindow.ScrollRow = 70 
ActiveWindow.ScrollRow = 71 
ActiveWindow.ScrollRow = 72 
ActiveWindow.ScrollRow = 73 
ActiveWindow.ScrollRow = 74 
ActiveWindow.ScrollRow = 75 
ActiveWindow.ScrollRow = 76 
ActiveWindow.ScrollRow = 77 
ActiveWindow.ScrollRow = 78 
ActiveWindow.ScrollRow = 79 
ActiveWindow.ScrollRow = 80 
ActiveWindow.ScrollRow = 82 
ActiveWindow.ScrollRow = 83 
ActiveWindow.ScrollRow = 84 
ActiveWindow.ScrollRow = 85 
ActiveWindow.ScrollRow = 87 
ActiveWindow.ScrollRow = 88 
ActiveWindow.ScrollRow = 89 
ActiveWindow.ScrollRow = 90 
ActiveWindow.ScrollRow = 91 
ActiveWindow.ScrollRow = 93 
ActiveWindow.ScrollRow = 94 
ActiveWindow.ScrollRow = 95 
ActiveWindow.ScrollRow = 96 
ActiveWindow.ScrollRow = 97 
ActiveWindow.ScrollRow = 98 
ActiveWindow.ScrollRow = 99 
ActiveWindow.ScrollRow = 100 
ActiveWindow.ScrollRow = 101 
ActiveWindow.ScrollRow = 102 
ActiveWindow.ScrollRow = 104 
ActiveWindow.ScrollRow = 106 
ActiveWindow.ScrollRow = 107 
ActiveWindow.ScrollRow = 109 
ActiveWindow.ScrollRow = 111 
ActiveWindow.ScrollRow = 112 
ActiveWindow.ScrollRow = 113 
ActiveWindow.ScrollRow = 114 
ActiveWindow.ScrollRow = 115 
ActiveWindow.ScrollRow = 117 
ActiveWindow.ScrollRow = 118 
ActiveWindow.ScrollRow = 119 
ActiveWindow.ScrollRow = 120 
ActiveWindow.ScrollRow = 122 
ActiveWindow.ScrollRow = 124 
ActiveWindow.ScrollRow = 125 
ActiveWindow.ScrollRow = 126 
ActiveWindow.ScrollRow = 127 
ActiveWindow.ScrollRow = 128 
ActiveWindow.ScrollRow = 129 
ActiveWindow.ScrollRow = 130 
ActiveWindow.ScrollRow = 131 
ActiveWindow.ScrollRow = 132 
ActiveWindow.ScrollRow = 133 
ActiveWindow.ScrollRow = 134 
ActiveWindow.ScrollRow = 135 
ActiveWindow.ScrollRow = 136 
ActiveWindow.ScrollRow = 137 
ActiveWindow.ScrollRow = 139 
ActiveWindow.ScrollRow = 141 
ActiveWindow.ScrollRow = 142 
ActiveWindow.ScrollRow = 143 
ActiveWindow.ScrollRow = 144 
ActiveWindow.ScrollRow = 145 
ActiveWindow.ScrollRow = 146 
ActiveWindow.ScrollRow = 147 
ActiveWindow.ScrollRow = 148 
ActiveWindow.ScrollRow = 149 
ActiveWindow.ScrollRow = 150 
ActiveWindow.ScrollRow = 151 
ActiveWindow.ScrollRow = 152 
ActiveWindow.ScrollRow = 153 
ActiveWindow.ScrollRow = 154 
ActiveWindow.ScrollRow = 155 
ActiveWindow.ScrollRow = 156 
ActiveWindow.ScrollRow = 157 
ActiveWindow.ScrollRow = 158 
ActiveWindow.ScrollRow = 159 
ActiveWindow.ScrollRow = 160 
Range("A194").Select 
ActiveSheet.Paste 
End Sub 

Solution


The full logic is not clear. I have just "modified" your macro so that you can understand the changes(though the whole macro can me tweaked a lot).

when you start the macro the macro will ask what number you want to search type there the invoice number e;.g. 33629

But I have one problem ok. you find 33629 in sheet2 and copy the entire row
goto sheet1(2) and paste the above row in the row where the first cell is A194 in the second sheet. ok so far.

But when you select another number where do you want to copy the row in sheet1(2). this is not clear.

I suggest you keep your original workbook safe some where so that it can be retrieved.
then run the macro and see whether it is ok. if it is ok then answer my qeustion in bold undelined above. the macro can be still more modified.
In the macro I have put a single apostrophe in the beginning of some lines so that they will nOT be operable. You can delete these lines later once you understand the macro
the macro at the present stage is

Sub TEST()
Dim j As Double
j = InputBox("type the number you want e.g. 33629")

'Range("AM5:AS5").Select
'ActiveCell.FormulaR1C1 = "33629"
Sheets("Sheet2").Select
Cells.Find(What:=j, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'Rows("6:6").Select
'Range("C6").Activate
'Selection.Copy
ActiveCell.EntireRow.Copy
Worksheets("sheet1(2)").Select
Range("A194").PasteSpecial
End Sub

Note


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « How to Create Macro to Search, Copy, & Paste? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.