title: Loop through files in folder and copy/paste to master file
body: <p>I have 3 files in a folder and one master template.</p>
  <p>I want to:</p>
  <ol>
  <li>Loop through these files then copy the content to a master file.</li>
  <li>Each WHOLE file will be pasted to a new worksheet in the master file.</li>
  <li>The new worksheet's name will be the same as file's name.</li>
  </ol>
  <p>The codes below are not working and missing functions 2 and 3.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub AllFiles()
  Application.EnableCancelKey = xlDisabled
  Dim folderPath As String
  Dim Filename As String
  Dim wb As Workbook
  Dim sh As Worksheet
  folderPath = &quot;C:\Users\Ryan\Desktop\LoopThroughFolders\Sample1\&quot; 'contains folder path
  If Right(folderPath, 1) &lt;&gt; &quot;\&quot; Then folderPath = folderPath + &quot;\&quot;
  Filename = Dir(folderPath &amp; &quot;*.xlsx&quot;)
  Do While Filename &lt;&gt; &quot;&quot;
      Application.ScreenUpdating = False
      
      Set wb = Workbooks.Open(folderPath &amp; Filename)
      
      Range(&quot;A1:A&quot; &amp; Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row).Copy
  
      'Not working well here as it will be overwritten by the next file 
      Workbooks(&quot;Master Template&quot;).Worksheets(&quot;Sheet1&quot;).Range(&quot;A1:A&quot; &amp; Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
      
      Workbooks(Filename).Close
      Filename = Dir
  Loop
     Application.ScreenUpdating = True
  End sub
  </code></pre>