Using dynamic file path instead of hard-coded

Hi

I am teaching myself VBA coding so hopefully my title and what I want to do make sense.

I have spent a lot of hours searching, testing etc but I am a bit stumped on this one. If someone could have a quick look and point me in the right direction it would be greatly appreciated.

I have two workbooks that will live in different folders, in this case TEST and TEST2.

In folder TEST I have a workbook named DRAWING REGISTER with data in the range A2 to B800, Column A is the drawing number and column B is the revision number.

I want to copy this data from this worksheet into another worksheet named DRAWING STATUS in the TEST 2 folder which at present I have hard-coded into the code I have so far.

Please Login or Register  to view this content.

I would like to have cell A2 in worksheet COMP (current data in that cell is 'C:\Users\Melly\Desktop\Test\Drawing Register.xlsx') in the VBA code to replace the hard-coded path. That way I can just replace the path to cell A2 and it will automatically update to the new path.

Two minor details I would like to address is when the DRAWING INDEX closes, I get a message asking if I want to save any changes, which I do not, as I am only pulling data from that worksheet.

The other is a message asking if I would like to keep the large amount of data on the clipboard, which once again I do not.

Hopefully I have explained well enough to get some help.

Thanks in advance, Anthony

for the closing of the workbook have you tried

Please Login or Register  to view this content.

How about

Please Login or Register  to view this content.

I have tried to add the two files as attachments but I think I fried my brain getting to where I did

If necessary I will try again after a bit of a break.

Hopefully someone smarter than me can decipher my questions above without them.

Legend, worked exactly as intended.

Greatly appreciate the help.

Glad we could help & thanks for the feedback

Sorry, I didn’t see your suggestion before I went ahead with Fluff13s solution below. Appreciate you taking time to help.