astrochemist
10-10-2020, 02:55 PM
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 End Sub
Any help/guidance will be greatly appreciated!
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 End Sub
Any help/guidance will be greatly appreciated!