title: Copying from multiple workbooks to single workbook Excel VBA
body: <p>I have multiple workbooks in a single folder. All the workbooks share the same format and I wish to copy from the same range on the first worksheet in all workbooks and add this to a single worksheet of a newly created workbook.</p>
  <p>The code so far:</p>
  <pre><code>Sub OpenAllCompletedFilesDirectory()
      Dim Folder As String, FileName As String
      Folder = &quot;pathway...&quot;
      FileName = Dir(Folder &amp; &quot;\*.xlsx&quot;)
      Do
          Dim currentWB As Workbook
          Set currentWB = Workbooks.Open(Folder &amp; &quot;\&quot; &amp; FileName)
          CopyDataToTotalsWorkbook currentWB
  
          FileName = Dir
      Loop Until FileName = &quot;&quot;
      
  End Sub
  
  Sub AddWorkbook()
      Dim TotalsWorkbook As Workbook
      Set TotalsWorkbook = Workbooks.Add
      outWorkbook.Sheets(&quot;Sheet1&quot;).Name = &quot;Totals&quot;
      outWorkbook.SaveAs FileName:=&quot;pathway...&quot;
   
  End Sub
  
  Sub CopyDataToTotalsWorkbook(argWB As Workbook)
      Dim wsDest As Worksheet
      Dim lDestLastRow As Long
      Dim TotalsBook As Workbook
      Set TotalsBook = Workbooks.Open(&quot;pathway...&quot;)
      Set wsDest = TotalsBook.Worksheets(&quot;Totals&quot;)
      
      Application.DisplayAlerts = False
     
      lDestLastRow = wsDest.Cells(wsDest.Rows.Count, &quot;A&quot;).End(xlUp).Offset(1).Row
      argWB.Worksheets(&quot;Weekly Totals&quot;).Range(&quot;A2:M6&quot;).Copy
      wsDest.Range(&quot;A&quot; &amp; lDestLastRow).PasteSpecial
      
      Application.DisplayAlerts = True
      TotalsBook.Save
  End Sub
  </code></pre>
  <p>This works - to a point. It does copy the correct ranges across and place the results one below another on the &quot;Totals&quot; worksheet of the &quot;Totals&quot; workbook, but it raises a 'Subscript out of range' error on:</p>
  <pre><code>argWB.Worksheets(&quot;Weekly Totals&quot;).Range(&quot;A2:M6&quot;).Copy
  </code></pre>
  <p>after data from the last workbook has been pasted.
  How can I tidy this code so that it works without error?
  I imagine there is scope to improve the code too.</p>