Blakieto,

I don't know who to give credit to for this. I didn't create it. When I got it you could just import data from cells and I changed it for you to get a range. I hope it works.

you will have 2 books for this example. mine were named main.xls and data.xls

Open a closed workbook in a given directory, retrieve data from it and close it without saving any changes to it.

target is the name of the target sheet for the data!! (in the main.xls excel book that you want to import data into)

Opens data.xls -retrieves data from data.xls-sheet1 range A10-B20 and brings it to range B10-C20 on sheet 1 in the main.xls

the option explicit is very important. and you will have to set up the retrieval ranges and target ranges to suit your purposes. Also the path to the data.xls which you can rename as long as you rename it in the code. I hope this isn't too much info.

put this in a new module


Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Temp\data.xls", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("target")
' read data from the source workbook
        .Range("B10", "C20").Formula = wb.Worksheets("Sheet1").Range("A10", "B20").Formula
End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub


I have zipped and attached the two files that I am working on for you to try. If you put them in C:\Temp and run the main.xls you should be able to see it work. Let me know if this doesn't work or isn't what you are looking for because there are some real programmers here who will help you. This place is a great resource.