Copy cells from another open workbook, little lost.

[Willow123]

Hi All,

So I would like, if possible, to be able to copy 2 columns of text (whose length will vary) from another open workbook into the my active workbook. I believe I have to use the index method of identifying the workbook that i would like to copy the column of text from as the name of that workbook will change. I cannot have anycode in the workbook i am copying from. I've been playing around with variations of the code below just trying to get one column to copy, to no avail. Any thoughts would be greatly appreciated.

[FONT=Verdana]Sub getinfo()[/FONT]
[FONT=Verdana]'Workbooks(2).Activate

Dim r() As Variant
Dim r1 As Range[/FONT]
[FONT=Verdana]
With Workbooks(2)
r1 = Range("H1", Range("H1").End(xlDown)).Select
End With[/FONT]
[FONT=Verdana]
Range("H1", Range("H1").End(xlDown)).Offset(0, 1).Select[/FONT]
[FONT=Verdana]
End Sub
[/FONT]

 

[JLGWhiz]

If you are going to use the index to identify your workbooks then you will need to make sure of the order in which the workbooks are opened, because Excel determines what the index number is, not the code you write. If the workbook containing the code is opened first and no other Excel files are open, then it will be Workbooks(1), the next to open will be Workbooks(2) and the index will follow in sequence as the workbooks are opened. If you close Workbooks(2), all the folloing indexes automatically decrease by 1. So working with indexes means you have to keep track of which workbooks are opened in which order and if any close during runtime of a procedure, the index of some of the open workbooks will change. The best way to handle that is to Set your workbooks to object variables at the beginning of the code and use that variable throughout the code so it will always refer to the correct workbook.

Subt()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = ThisWorkbook 'assues opened first
Set wb2 = Workbooks(2)
Set sh1 = wb1.Sheets(1) 'variable now contains workbook and sheet reference
Set sh2 = wb2.Sheets(1) 'same here
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

 

[Willow123]

If you are going to use the index to identify your workbooks then you will need to make sure of the order in which the workbooks are opened, because Excel determines what the index number is, not the code you write. If the workbook containing the code is opened first and no other Excel files are open, then it will be Workbooks(1), the next to open will be Workbooks(2) and the index will follow in sequence as the workbooks are opened. If you close Workbooks(2), all the folloing indexes automatically decrease by 1. So working with indexes means you have to keep track of which workbooks are opened in which order and if any close during runtime of a procedure, the index of some of the open workbooks will change. The best way to handle that is to Set your workbooks to object variables at the beginning of the code and use that variable throughout the code so it will always refer to the correct workbook.

Subt()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = ThisWorkbook 'assues opened first
Set wb2 = Workbooks(2)
Set sh1 = wb1.Sheets(1) 'variable now contains workbook and sheet reference
Set sh2 = wb2.Sheets(1) 'same here
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

Thanks for the help JLG that works great.

 

[JLGWhiz]

Thanks for the help JLG that works great.

You're welcome,
regards, JLG