Windows Excel macro(?) help

I'm in the process of simplifying how the company I work for records serial numbers for all our stock.

I basically want to enter the invoice number for the order into A2 then all the serials for that invoice into A4. Then I want to run a macro that finds A2's value in a second sheet and then copies A4 into the cell to the right of the invoice number on the second sheet (I don't mind if this is given as a column F value as that's the column it will end up in).

If that makes sense and you feel the unnerving need to help, I'd be very greatful

 

What you are looking for, unless I'm picking this up wrong, is a simple vlookup
https://support.office.com/en-in/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65

Vlookups can operate across worksheets, and workbooks so should be just fine.

 

I did think of a vlookup (or an INDEX/MATCH) but I'd have to enter the formula into every cell in the second sheet's F column and once the serials had been removed from the first sheet (it's meant to be like an input sheet, the values won't remain on this sheet), I think the vlookups would stop working, wouldn't they?

Edit:
I want to copy the highlighted cells on Sheet 1 (I'm going to put it all in one cell for ease of use)


And then paste them next to the corresponding invoice number on Sheet 2 (cell F1059 in this case)


I don't think I can use vlookup in this instance, can I?

 

I was about to suggest index, match but then saw your latest post.

Put another formula in your entry sheet to Match your A2 data and return the row number in the second sheet Lets say it goes in B2

Concatenate all the serials into a single cell. Lets say it goes in C2

Then use your macro from the data entry sheet:

Define variables to manage rows and columns with: e.g.


Sub whatevermacroname

Dim r As Integer
Dim c As Integer

r = ActiveSheet.Range("B2").Value
c = 6 correct to the number for the column you want to paste into if not Column F

Range("C2").Select
Selection.Copy
Windows("destinationworkbookname.xlsm").Activate
Range(r,c).Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
Windows("sourceworkbookname.xlsm").Activate

MsgBox "Serials copied successfully"

End Sub

this is untested and pretty off the cuff as I only bothered to pull the paste special syntax off an old macro here

I'd probably have to play around to do it properly but maybe this sets you on a path to do it. I've assumed both workbooks are open on the machine when the macro is run from the data entry sheet

Hope this helps


If you want to get more adventurous then you could probably define more variables and use a while statement. e.g.
while row <>""
while invoice number matches invoice number in other workbook then paste data
Else increment row by 1
End while
End while

 

Thanks GMC, that certainly helps massively. I'll try it out when I get to work on Tuesday and will report back

I'm fine with formulas but macros etc are something I've never had to do.

 

Quick update, GMC's help got me going in the correct direction. I altered the code a little bit (I'll post the finished version if someone really wants to see it) and now it all works flawlessly

 

Glad to hear it
Please do post the final script if you can. I tend to gather such things to cannibalise as I need to do things.