title: Append variable ranges to array vba
body: <p>I've got a series of ranges I'm copying from separate workbooks into a single sheet. Currently I'm doing this by range copy/paste, but with 3 workbooks it takes forever to update. 
  I want to change this into a single array that can take the used range from each sheet, append it to the array, and then psate the array into my table.</p>
  
  <p>Current code:</p>
  
  <pre><code>Sub UpdateTable()
  Dim icounter As Long
  Dim x As Workbook 'The book we're in
  Dim y As Workbook 'The data from P6
  Dim z As Workbook
  Dim w As Workbook
  
  Set x = ThisWorkbook
  
  Set y = Workbooks.Open("W:\AOPS\Scheduling\Allan Dunn\P6 Output Folder\6011-Activities.xls")
  Set z = Workbooks.Open("W:\AOPS\Scheduling\Allan Dunn\P6 Output Folder\6006-Activities.xls")
  Set w = Workbooks.Open("W:\AOPS\Scheduling\Allan Dunn\P6 Output Folder\MCR4-Activities.xls")
  
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  
  
  'Copy-paste the values from the P6 output sheets to the workbook
  y.Sheets("TASK").Range("A3:J3000").Copy
  
  x.Sheets("TASKS").Range("A2").PasteSpecial
  
  Application.CutCopyMode = False
  
  z.Sheets("TASK").Range("A3:J300").Copy
  
  x.Sheets("TASKS").Range("A3001").PasteSpecial
  
  Application.CutCopyMode = False
  
  w.Sheets("TASK").Range("A3:J300").Copy
  
  x.Sheets("TASKS").Range("A3300").PasteSpecial
  
  Application.CutCopyMode = False
  
  'Close the output sheets
  y.Close
  z.Close
  w.Close
  
  
  
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  
  
  End Sub
  </code></pre>
  
  <p>As you can see, it's somewhat messy, and it takes a long time to run (almost a full minute, given the size of the ranges). </p>
  
  <p>The reason I've chosen the ranges to be this large is because I do not know how many items (rows) will be coming out in each worksheet. The columns will always remain the same, but the rows are subject to change.</p>
  
  <p>Thanks</p>