How to copy data from another workbook (excel)?

Question

I already have a macro that creates sheets and some other stuff. After a sheet has been created do I want to call another macro that copies data from a second excel (its open) to first and active excel file.

First I want to copy to headers, but I cant get that to work - keep getting errors.

Sub CopyData(sheetName as String)
  Dim File as String, SheetData as String

  File = "my file.xls"
  SheetData = "name of sheet where data is"

  # Copy headers to sheetName in main file
  Workbooks(File).Worksheets(SheetData).Range("A1").Select  # fails here: Method Select for class Range failed
  Workbooks(File).Worksheets(SheetData).Range(Selection, Selection.End(xlToRight)).Select
  Workbooks(File).Worksheets(SheetData).Selection.Copy ActiveWorkbook.Sheets(sheetName).Cells(1, 1)
End Sub

What is wrong ?

I really want to avoid having to make "my file.xls" active.

Edit: I had to give it up and copy the SheetData to target file as a new sheet, before it could work. Find and select multiple rows

Answer 1

Two years later (Found this on Google, so for anyone else)... As has been mentioned above, you don't need to select anything. These three lines:

Workbooks(File).Worksheets(SheetData).Range("A1").Select
Workbooks(File).Worksheets(SheetData).Range(Selection, Selection.End(xlToRight)).Select
Workbooks(File).Worksheets(SheetData).Selection.Copy ActiveWorkbook.Sheets(sheetName).Cells(1, 1)

Can be replaced with

Workbooks(File).Worksheets(SheetData).Range(Workbooks(File).Worksheets(SheetData). _
Range("A1"), Workbooks(File).Worksheets(SheetData).Range("A1").End(xlToRight)).Copy _
Destination:=ActiveWorkbook.Sheets(sheetName).Cells(1, 1)

This should get around the select error.

Answer 2

Best practice is to open the source file (with a false visible status if you don't want to be bother) read your data and then we close it.

A working and clean code is avalaible on the link below :

http://vba-useful.blogspot.fr/2013/12/how-do-i-retrieve-data-from-another.html

Answer 3

Would you be happy to make "my file.xls" active if it didn't affect the screen? Turning off screen updating is the way to achieve this, it also has performance improvements (significant if you are doing looping while switching around worksheets / workbooks).

The command to do this is:

    Application.ScreenUpdating = False

Don't forget to turn it back to True when your macros is finished.