Hey guys,

I need a somewhat complicated VBA code hats off to you if you know how to do it because I can't. I really appreciate any replies as I am in desperate-mode right now....I took time to fill the workbooks with fictitious information so you can follow, in reality they will both go on till row 1000 but you get my point I hope.

I have two different files, one will be active called "MainFile" where the data will be pasted and the one which data will be pulled from called DataSource, DataSource will be closed at the time of performing this macro but will be saved in the same spot all the time (the file path leave as "C:\filepath" ill enter it myself).


I need the code to look at the contract name in Column A of Workbook “MainFile”, do an HLOOKUP in DataSource for that Contract name (there will be a match) and copy & paste (values only) the MIS number to Column B of that corresponding Contract Name on the MainFile, and than the code will close the DataSource workbook.... The catch is as you can see the rows are merged by five because further down it stems out to single rows, really stuck here how to do this. Please help...File are attached

MainFile is this
ScreenShot Main.png

DataSource is this
ScreenShot Data.png


MainFile.xlsmDataSource.xls