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.

Sub Update_Register()
Dim Status As Workbook
Dim Draw_Index As Workbook
Dim Status_Path As String
Dim Draw_Index_Path As String


Status_Path = Sheets("COMP").Range("A2")

Worksheets("DI").Activate

' Open both workbooks first:
Set Status = Workbooks.Open("C:\Users\Melly\Desktop\Test2\DRAWING STATUS.xlsm")
Set Draw_Index = Workbooks.Open("C:\Users\Melly\Desktop\Test\Drawing Register.xlsx")
   
'Copy drawing number from Draw_Index:
Draw_Index.Sheets(2).Range("A2:A800").Copy

'Paste to Status worksheet:
Status.Sheets(2).Range("A2:A800").PasteSpecial xlPasteValues

'Copy revision number from Draw_Index:
Draw_Index.Sheets(2).Range("D2:D800").Copy

'Paste to Status worksheet:
Status.Sheets(2).Range("B2:B800").PasteSpecial xlPasteValues

'Close Draw_Index:
Draw_Index.Close

End Sub
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