Using dynamic file path instead of hard-coded

[robbo1172]

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

[nigelog]

for the closing of the workbook have you tried

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[robbo1172]

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.

[robbo1172]

Legend, worked exactly as intended.

Greatly appreciate the help.

[Fluff13]

Glad we could help & thanks for the feedback

[robbo1172]

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