Search for code in another worksheet and copy information back

[Pango]

Hi there,

I've got a spreadsheet (Workbook 1) which contains product information across a number of columns, each row being a unique product with a unique product code attached. Separately, there is another spreadsheet (Workbook 2) with sales data for each month which references each product sold by the same unique product code. The unique product code is in column A in Workbook 1, and column N in Workbook 2. All information required is on Sheet 1 of each workbook.

I need a macro to, for each row of Workbook 2, search for the product code in Workbook 1 and then copy some information from the relevant rows back to Workbook 2.

e.g. if workbook 1 looks like this:
Col A Col C Col G
A1 blue w
A2 black x
A3 yellow y
A4 green z

And workbook 2 looks like this:
Col A Col B
12 A1
3 A3
56 A4
2 A2

I need Workbook 2 to come out like:

Col A Col B Col N Col O
12 A1 blue w
3 A3 yellow y
56 A4 green z
2 A2 black x

Note that the cells being copied from Workbook 1 might not be next to each other (e.g. Col C and Col G) but the place they will be copied to in Workbook 2 would be (e.g. Col N, Col O). Both sets of columns are at set places that will never change.
Also, they are different workbooks, saved in different places. I don't mind having to open Workbook 1 before running the process, but I can't copy the information from Workbook 1 onto a sheet in Workbook 2 for security reasons.

I'm competent with macros within one workbook, but I have difficulty working with other workbooks. Below is the structure of how I would expect it to run, the two lines that are commented out are the lines that aren't real code because I don't know how to write it.

Please Login or Register  to view this content.

Can anyone help with the commented out lines? Or give me guidance for how to work with other workbooks in this way?


Thanks,
Pango

[AlphaFrog]

This is not tested, but it should get you pointed in the right direction.

Please Login or Register  to view this content.

[Pango]

Thank you so much AlphaFrog, I see how the code would work now - I haven't been able to test it yet but I'll post here when I have