Thread: Copying data from one workbook to another?

Hello

I am trying, once again, to avoid using copy paste in my VBA code, and have run into a problem that i cant solve.

I have a workbook called Book1, from a VBA code within this workbook i want to open another workbook, copy some data from this other workbook into the first one and close workbook two again.

i have tried the following code but gets the failure notice:

Run-time error '9':

Subscript out of range

Sub Retrieve_data_from_Book2()
    Application.Workbooks.Open ("c:/Documents and Settings\XXXX\Desktop\Book2.xls")
    
    Sheets("Table").Select
    Range("F15:v7319").Copy Windows("Book1.xls").Sheets("Sheet1").Range("A1")
    
    Workbooks("Book2.xls").Activate
    ActiveWorkbook.Close
    
    Windows("Book1.xls").Activate
   
End Sub

Can anybody help me please?

/Freakazoid

When you DEBUG at the error message, which line of code is highlighted? This error frequently means it can't find something you've defined on that line of code, like a workbook name or a sheet name is wrong or unavailable in some way.

Maybe this:

Sub Retrieve_data_from_Book2()
Dim wb1 As Workbook
Dim wb2 As Workbook

    Set wb1 = ThisWorkbook
    Set wb2 = Application.Workbooks.Open ("C:/Documents and Settings\XXXX\Desktop\Book2.xls")
    
    Sheets("Table").Range("F15:V7319").Copy wb1.Sheets("Sheet1").Range("A1")
    
    wb2.Close False

End Sub