VBA help on pulling data from an external workbook

[astrochemist]

I have two files attached to similarly represent what I am working with since I cannot share them. The code is below used in book2.xlsm

I am using the "copy data" macro in book2.xlsm to pull data from text.xlsx and it works well for the "data entry" sheet on test.xlsx.

I want this to work for the sample 1, sample 2, etc sheets as well though so that I am able to pull the integration values. However, the name of the sample sheet (sample 1 in text.xlsx) will change with the name of the sample and thus needs to be dynamic. It will match the sample name in book2 though. How can I achieve this?

Additionally, how can I change the code for the external workbook so that I can reference cell A1 for the file name instead of having to code in a new file name every time? This will be a very routine operation I will do daily with new file names for samples I process.

Sub copydata() 
Dim rw As Long, x As Range 
Dim extwbk As Workbook, twb As WorkbookSet twb = ThisWorkbook
Set extwbk = Workbooks.Open("/Users/username/desktop/test.xlsx")
Set x = extwbk.Worksheets("Data entry").Range("A1:GZ400")

With twb.Sheets("Sheet1")

    For rw = 4 To .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 11, False)
    Next rw

End With

With twb.Sheets("sheet1")

         For rw = 4 To .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(rw, 3) = Application.VLookup(.Cells(rw, 1).Value2, x, 12, False)
    Next rw

End With

    extwbk.Close savechanges:=False [COLOR=var(--black-800)]End Sub[/COLOR]

Any help/guidance will be greatly appreciated!