title: VBA - Copying and Pasting from Multiple Excel files to Single Excel File
body: <p>Long time reader and admirer of StackOverflow.</p>
  
  <p>Basically I am trying to to loop through a series of Excel files to copy a range of data and paste it on a single Excel workbook/sheet.</p>
  
  <p>The cell range location (C3:D8, D3:E8) is not always consistent, but the table dimensions are: 29 R x 2 C. Also, the files only have 1 sheet, and aside from the table dimensions specified, no data values in other cells.</p>
  
  <p>In its current form the code is executing, but not pasting anything to its destination Excel file.</p>
  
  <p>I need it to</p>
  
  <ol>
  <li>Find the data dimension in file (table)</li>
  <li>Copy the table</li>
  <li>Paste to destination (below previous table)</li>
  <li>Loop through to next file </li>
  <li>Repeat Step 1-4</li>
  </ol>
  
  <p>The code is from:
  <a href="https://stackoverflow.com/questions/3736417/excel-vba-automating-copying-ranges-from-different-workbooks-into-one-final-des">Excel VBA: automating copying ranges from different workbooks into one final destination sheet?</a></p>
  
  <p>Thanks a lot for any help, I really appreciate it and please feel tell me to specify anything if my question is vague.</p>
  
  <pre><code>Sub SourcetoDest()
  
      Dim wbDest As Workbook
      Dim wbSource As Workbook
      Dim sDestPath As String
      Dim sSourcePath As String
      Dim shDest As Worksheet
      Dim rDest As Range
      Dim vaFiles As Variant
      Dim i As Long
  
      'array of folder names under sDestPath
  
      'array of file names under vaFiles
      vaFiles = Array("Book1.xls")
  
      sDestPath = "C:\Users"
      sSourcePath = "C:\Users"
  
  
      Set wbDest = Workbooks.Open(sDestPath &amp; "\" &amp; "Book2.xlsm")
      Set shDest = wbDest.Sheets(1)
  
      'loop through the files
      For i = LBound(vaFiles) To UBound(vaFiles)
          'open the source
          Set wbSource = Workbooks.Open(sSourcePath &amp; "\" &amp; vaFiles(i))
  
          'find the next cell in col C
          Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
          'write the values from source into destination
          rDest.Resize(5, 1).Value = wbSource.Sheets(1).Range("C7:D33").Value
  
  
          wbSource.Close False
      Next i
  
  End Sub
  </code></pre>