Macro to loop through one workbook, search another book, output info from 2nd book into 1st

[DrParmeJohnson]

I'm trying to make a macro that uses two workbooks, one just being the active book and the other being named "ASIN to LD.xlsx", which would be opened and hidden by XLSTART.

It uses two key columns in the other workbook (ASIN to LD). The two columns are columns A and B and A1 has "ASIN" in it and B1 has "Item#" in it.

So, the macro, first, would run on the active workbook (the name of the book varies as it changes depending on where it comes from), where it would find the column with ASIN in the first cell (again, this column with ASIN in the first cell would vary in its column location as the workbooks we use vary).

It would then look at the each cell of this column (in the active book) and for each cell, it would take the value of said cell (which is a combination of numbers and letters) and then would look to the other workbook (ASIN to LD.xlsx) on Sheet1 (named Sheet1), and search through column A, which has "ASIN" in A1, until it finds the value it found in the cell from the active workbook.

Then, when it finds that matching value, it would look at the cell directly to the right of the matching cell in the B column, which has Item# in B1, and copy the value in the adjacent cell to a new column on the active workbook directly to the right of the column which contains ASIN in the first cell (say the column with ASIN in it were column G, ASIN would be G1, it'd insert a new column H with H1 as "Item#").

It would then take that value it found on the second workbook (ASIN to LD) and paste it into, say the initial value on the active workbook was found in G2, paste the value found and copied in the second book into the new column, directly beside the initial value that was searched for.

It would then repeat this process of searching, matching and inserting until it reaches the end of the column with ASIN at the top on the active workbook.

Here attached is a rough video of the process mentioned but done manually as to show what is meant:

https://imgur.com/a/8TfElwY

It is in 3 parts because of how imgur is with video length but they should be labeled accordingly.

But, keep in mind that even tho the column with ASIN in the active book in this video is column B, it is not always the same and so it would need to be dynamic.

I'm sorry for such a long post and large request but I can only describe in code terms what the macro would do, not write it.

Thanks in advance.

 

[DrParmeJohnson]

Bumping. Been about a week since posting, if there is any need for clarification, please do ask. Thanks.

 

[DrParmeJohnson]

Bumping again, thanks.