How To Create Macro to Search, Copy, & Paste [Solved/Closed]

- - Latest reply: rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Feb 10, 2010 at 05:26 PM
Hello,

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

:)
See more 

3 replies

Best answer
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
1
Thank you
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

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5641 users have said thank you to us this month

0
Thank you
Thanks :) Is there a code I could add to it to tell it. If cancel is pressed or if the top corner x is press not to glitch out. Just to cancel macro?
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
754
0
Thank you
Just underneath

j = InputBox("type the number you want e.g. 33629")

you can add

If (j = "") Then Exit Sub