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.
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.
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
Dim j As Double
j = InputBox("type the number you want e.g. 33629")
'ActiveCell.FormulaR1C1 = "33629"
Cells.Find(What:=j, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _