VBA - Find values in other workbook

[Kenn]

Hello. I've got 2 workbooks. Workbook 01 gets updated each day by other staff. I need a macro in Workbook 02 to get information from workbook 01 as follows..

Workbook 01 is like a master file with a list of 1000's of part numbers & associated information.

Workbook 02, B6:B25 contains a list of 20 unsorted part numbers.

I need a macro in Workbook 02 to

1. Read the value of B6 in workbook 02
2. Search for this value in Workbook 01 column H
3. When found, copy the value in Workbook 01 column K
4. Go back to Workbook 02 B6 & find the next empty cell to the right & paste
5. Repeat for B7 to B25

It's like a vlookup function, but I need it in <acronym title="Visual Basic for Applications" style="color: rgb(0, 0, 0); font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; font-size: 13.600000381469727px;">VBA</acronym>.


I would really appreciate any help or suggestions with this.

Thank you
Kenn.

Using - MSE 2010.

 

[L. Howard]

Try this where code goes into workbook 2 and the sheet with the Range("B6:B25") list.
Change [Book4] to the other workbook name. [MyBook] or whatever it is.
Use conditional formatting to hid #N/A errors if no match, if you want.

Option Explicit
Sub Bk4_To_Bk3()
With Sheets("Sheet2").Range("B6:B25")
   .Formula = "=VLOOKUP(H1,[Book4]Sheet1!$A$1:$B$25,2,0)"
   .Value = .Value
End With
End Sub

Regards,
Howard