title: Excel VBA - loop over files in folder, copy range, paste in this workbook
body: <p>I have 500 excel files with data. I would merge all this data into one file. </p>
  
  <p>Task list to achieve this:</p>
  
  <ol>
  <li>I want to loop over all the files in a folder</li>
  <li>open the file, </li>
  <li>copy this range "B3:I102"</li>
  <li>paste it into the 1st sheet of the active workbook</li>
  <li>repeat but paste new data underneath</li>
  </ol>
  
  <p>I've done task 1-4 but i need help with task 5, last bit - pasting the data under the existing data and making it dynamic. I've highlighted this bit with '#### in my code.</p>
  
  <p>Here is my code which I've put together from other people's question :)</p>
  
  <p>Any suggestions on how to do this?</p>
  
  <pre><code>Sub LoopThroughFiles()
  Dim MyObj As Object, 
  MySource As Object, 
  file As Variant
  Dim wbThis                  As Workbook     'workbook where the data is to be pasted, aka Master file
  Dim wbTarget                As Workbook     'workbook from where the data is to be copied from, aka Overnights file
  Dim LastRow As Long
  Dim sht1 As Worksheet
  Dim sht2 As Worksheet
  
  'set to the current active workbook (the source book, the Master!)
  Set wbThis = ActiveWorkbook
  Set sht1 = wbThis.Sheets("Sheet1")
  
  Folder = "\\dne\ldc\research-dept\3 CEEMEA\15. EMB\Turkey\TLC Overnight &amp; Weekly Reports\weekly (majeed)\"
  Fname = Dir(Folder)
  
  While (Fname &lt;&gt; "")
  
    Set wbTarget = Workbooks.Open(Filename:=Folder &amp; Fname)
    wbTarget.Activate
    Range("b3:i102").Copy
  
    wbThis.Activate
  
    '################################
    'NEED HELP HERE. I GET A ERROR HERE. NEEDS TO BE MORE DYNAMIC.
    sht1.Range("b1:i100").PasteSpecial
  
   Fname = Dir
  
   'close the overnight's file
    wbTarget.Close
   Wend
  
  End Sub
  </code></pre>