How To Create Macro to Search, Copy, & PasteSolved/Closed

Question

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

:)

venkat1926 · Answer

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

PaulBehr · Answer

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?

rizvisa1 · Answer

Just underneath 

j = InputBox("type the number you want e.g. 33629")

you can add

If (j = "") Then Exit Sub

How To Create Macro to Search, Copy, & Paste

3 responses

Similar discussions

Subscribe To Our Newsletter!