How do I copy between two workbooks in Excel?

Question

I'm trying to copy some information from one workbook to another in Excel. I hate this language, I can't figure out when to use activate when not to, etc...

Here is my code that is giving me a run-time error 1004. I would like to get the copy and paste to work without switching back and forth between sheets. My reference for the code is here.

Here is my actual code:

Option Base 1

Sub populate_acc_template()
'
' populate_acc_template Macro
' Customer:  ACC.  This template populates the spreadsheet with data from an HRIS sheet.
'

' Start by defining the book to be pulled from and getting the first and last rows
' of that book.

    Dim template_book As Workbook
    Set template_book = ThisWorkbook
    Dim pull_book As Workbook
    Set pull_book = Workbooks.Open(Application.ActiveWorkbook.Path & "\books\sample_book.xlsx")

    With ActiveSheet
        FirstRow = 2
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    insert_length = LastRow - FirstRow


' Now insert the number of rows that we need in the template.

    template_book.Sheets("Promotion Calculations").Rows(9 & ":" & 9 + insert_length).Insert Shift:=xlDown


' Copy and paste the information from the bulk data.

    Dim paste_array(1 To 9) As String

    paste_array(1) = 5
    paste_array(2) = 6
    paste_array(3) = 4
    paste_array(4) = 9
    paste_array(5) = 10
    paste_array(6) = 3
    paste_array(7) = 2
    paste_array(8) = 7
    paste_array(9) = 8

    For i = 1 To UBound(paste_array)

        ' Copy the entire column containing text.
         template_book.Sheets("Promotion Calculations").Range(Cells(8, paste_array(i)), Cells(8 + insert_length, paste_array(i))).Value = pull_book.Sheets("Data Sheet").Range(Cells(FirstRow, i), Cells(LastRow, i))
    Next i
End Sub

*can't figure out when to use activate when not to* the answer is **never** use `Activate` :) — David Zemens, Sep 03 '15 at 19:01
Sorry about that. The 1004 error in this case is almost *always* related to an improperly defined range object. Break down and debug this statement: `template_book.Sheets("Promotion Calculations").Range(Cells(8, paste_array(1)), Cells(8 + insert_length, paste_array(1))).Value = pull_book.Sheets("Data Sheet").Range(Cells(FirstRow, i), Cells(LastRow, i))` and that is most likely the source of your problem. — David Zemens, Sep 03 '15 at 19:05
I know that's where it is but it literally makes no sense to me as the part above it, `template_book.Sheets("Promotion Calculations").Rows(9 & ":" & 9 + insert_length).Insert Shift:=xlDown` doesn't have any issue running... — rdevn00b, Sep 03 '15 at 19:07
What happens if you do: `Debug.Print template_book.Sheets("Promotion Calculations").Range(Cells(8, paste_array(1)), Cells(8 + insert_length, paste_array(1))).Address` — David Zemens, Sep 03 '15 at 19:07
Likewise, what if you do: `Debug.Print pull_book.Sheets("Data Sheet").Range(Cells(FirstRow, i), Cells(LastRow, i)).Address`? — David Zemens, Sep 03 '15 at 19:08
For `Debug.Print template_book.Sheets("Promotion Calculations").Range(Cells(8, paste_array(1)), Cells(8 + insert_length, paste_array(1))).Address` it ALSO produces "$A$2:$A$355" which is NOT correct. — rdevn00b, Sep 03 '15 at 19:10
For .activate http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros I've only ever found it to be an issue with .select. I've never seen and issue if you have declared a worksheet an you activate the object. — MatthewD, Sep 03 '15 at 19:25

David Zemens · Answer 1 · 2015-09-03T20:38:21.213

0

One problem is this statement (there may be others, but this is obviously a problem) as it appears you have unqualified Cells within Range assignments.

template_book.Sheets("Promotion Calculations").Range(Cells(8, paste_array(i)), Cells(8 + insert_length, paste_array(i))).Value = pull_book.Sheets("Data Sheet").Range(Cells(FirstRow, i), Cells(LastRow, i))
Next i

When not qualified, Cells always implicitly refers to ActiveSheet.Cells, so, the problem is that you are essentially doing:

Sheet1.Range(Sheet1.Cells(1,1), Sheet1.Cells(1,2)).Value = Sheet2.Range(Sheet1.Cells(1,1), Sheet1.Cells(1,2)).Value

Since the Cells on Sheet1 cannot produce a Range on Sheet2, the 1004 error raises.

I find it usually easier, and cleaner to read/interpret, if you explicitly define the ranges for copy/paste:

Dim pasteRange as Range
Dim copyRange as Range

For i = 1 To UBound(paste_array)
    With template_book.Sheets("Promotion Calculations")
        Set pasteRange = .Cells(8, paste_array(i)).Resize(insert_length, 1)
    End WIth

    With pull_book.Sheets("Data Sheet")
        Set copyRange = .Range(.Cells(FirstRow, i), .Cells(LastRow, i))
    End With

    pasteRange.Value = copyRange.Value

Next

edited Sep 03 '15 at 20:38

answered Sep 03 '15 at 19:23

David Zemens

53,033
11
81
130

It's also weird because if I put a Workbooks.Open statement at the top of the code (instead of thisworkbook) then I get an error stating that my file doesn't exist...strange – rdevn00b Sep 03 '15 at 19:28
See slight revision to make sure you're putting it in the `For i ... ` loop properly. Then tell me which line raises the error, if any. – David Zemens Sep 03 '15 at 19:32
Not even inserting a simple `template_book.Sheets("Promotion Calculations").Range("A3:C50").Select` above the for loop is working – rdevn00b Sep 03 '15 at 19:35
You can't *Select* something in an inactive workbook. Opening the `pull_book` has made that one the *active* workbook. This is why it is recommended not to rely on Selection or Activate/Active methods. It quickly becomes impossible to keep track of what you're doing, to what file. – David Zemens Sep 03 '15 at 19:39
Why is the `template_book.Sheets("Promotion Calculations").Rows(9 & ":" & 9 + insert_length).Insert Shift:=xlDown` line directly above working? If Promotion Calculations didn't exist this would not work. Additionally, I have verified that the sheet is not protected by looking in File. – rdevn00b Sep 03 '15 at 19:41
You can refer to the object (workbook, worksheet, range, etc.) when it is not *active*, but you cannot *select* it unless its parent is active. So, that is why your `.Insert` statement works, but the `.Select` statement fails. – David Zemens Sep 03 '15 at 19:47
See slight revision to make sure you're putting it in the For i ... loop properly. Then tell me which line raises the error, if any – David Zemens Sep 03 '15 at 19:47
Right so i still don't see why my code is failing in the loop – rdevn00b Sep 03 '15 at 20:30
I've now asked you twice, and you have not answered, so perhaps third time's a charm: **See slight revision to make sure you're putting it in the For i ... loop properly. Then tell me which line raises the error**. – David Zemens Sep 03 '15 at 20:33

How do I copy between two workbooks in Excel?

1 Answers1