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. http://stackoverflow.com/questions/482889/how-to-find-and-select-multiple-rows-with-macro-excel

Answer 1

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.

Answer 2

I don't think you need to select anything at all. I opened two blank workbooks Book1 and Book2, put the value "A" in Range("A1") of Sheet1 in Book2, and submitted the following code in the immediate window -

Workbooks(2).Worksheets(1).Range("A1").Copy Workbooks(1).Worksheets(1).Range("A1")

The Range("A1") in Sheet1 of Book1 now contains "A".

Also, given the fact that in your code you are trying to copy from the ActiveWorkbook to "myfile.xls", the order seems to be reversed as the Copy method should be applied to a range in the ActiveWorkbook, and the destination (argument to the Copy function) should be the appropriate range in "myfile.xls".