Calling External Worksheet and Setting Variable for Active Workbook

[jhuang5132]

I have this following code:

Please Login or Register  to view this content.

I am using VLookup to pull data from an external workbook called "CONTROL SHEET" into my workbook called "TEST FILE". The macro will be run individually on files so the file name will keep needing to change. I tried assigning a variable to call my workbook, but when I tried setting a variable to ActiveWorkbook, it didn't work. How would I correct this so that if the file name changes, I can still pull the data.

EDIT 1: I was able to resolve the issue below. I just had to remove the [ in front of the N, and add the [ before CONTROL. I still need help with the above.

Also, I currently need to have the "CONTROL SHEET" book open in order to run the macro. If it isn't, it asks me where the file is located. The file is located on a shared drive so if I could link to that instead, that would be awesome.

I tried this:

Please Login or Register  to view this content.

but in the actual workbook, the formula shows up something like: (I changed the name of the drives) The last letter of CONTROL gets cut off (it was a longer word so maybe it was too long?).

Please Login or Register  to view this content.

EDIT2: Solved first problem. I noticed that I could delete

Please Login or Register  to view this content.

and still have it work.