title: How do I copy between two workbooks in Excel?
body: <p>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...</p>
  
  <p>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 <a href="https://stackoverflow.com/questions/19351832/vba-copy-from-one-workbook-and-paste-into-another">here</a>.</p>
  
  <p>Here is my actual code:</p>
  
  <pre><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 &amp; "\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 &amp; ":" &amp; 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
  </code></pre>