referencing an HLOOKUP on another Workbook, Pasting Results on Active Workbook

Hi All,

I have a very hard macro that I am trying to figure out and I am breaking my head over it. I am not an expert at VBA. If someone can please help I would appreciate that so much. I will try my best to explain what I was assigned to do.

I have one Excel file, let's call it my "Main File" and another worksheet that will be saved in the same filepath but not opened when I am running this macro. Let's call this my "Data File".
The Main File looks like this...
002- DASHBOARD.PNG

The Data File looks like this...
002- DATASHEET.PNG

On the main file there are a lot of different columns with different information. What I want is to write a HLOOKUP or any fucntion you may know, that if there is a match in the "Code" column in the Main File with the Data File (I.e. there codes match up) than I need it to retrieve the "Contract Worker" name and paste it in column L of that coresponding row on the Main File.

The table array for the vlookup is Range("A1:AK1345")
The closed Data File path is "C:\Users\673157897\Documents\Pro Fees Dash Board\Copy of RBC_CWM_DHR_Details_Report___Thomson__A (Preview).xls"


Also I don't want the Data file to stay open, I want it to close when macro is done.

To recap: I need it to scan the Data File 'code' column and if it matches with any codes on the Main File, simply paste Contract Worker name in deisred place (column L) of that row, and close the DataFile.


Please help, I am on the verge of lossing it. I appreciate you taking your time for this your a great person!

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

also post a workbook:

(How to) Attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)