Solution to return vlookup as a value

[jcbklyny]

Hello everyone. I'm having a hard time figuring this out. I need to do a vlookup from one sheet to another but need the data returned as a value (paste special), not a formula.

I have to pull from a column that will track inventory levels of material that will be changing. If multiple lines are entered for this material a simple vlookup will keep changing the return for every line. I need this data particular column to display static data at the moment material is entered.

I'm assuming VBA... just can't for the life of me figure out the code. Please help!

Thank you

 

[jcbklyny]

FYI, this will be between 2 sheets in the same workbook.

 

[jcbklyny]

suggestions?