title: Loop Through Directory VBA to Copy Data With Format
body: <p>I have few files in a directory or a folder and I want to copy a range (values with format to the current sheet). I have VBA code and I think it is not in order or something is missing in the code. Please help me to fix the issue. </p>
  
  <p>(I have defined named range in each files in the directory. Is it is possible to copy using the named range?) </p>
  
  <p>Copy from directory files given path &amp; from sheet2 &amp; paste it to file "workbook.xlsm" Sheet "sheet1"</p>
  
  <pre><code>Sub LoopThroughDirectory()
      Dim MyFile As String
      Dim erow
      Dim Filepath As String
      Filepath = "C:\test"
      MyFile = Dir(Filepath)
      Do While Len(MyFile) &gt; 0
          If MyFile = "workbook.xlsm" Then
              Exit Sub
          End If
  
          Workbooks.Open (Filepath &amp; MyFile)
          Sheets("Sheet2").Select
          Range("A1:N24").Copy
          Workbooks.Open ("Filepath &amp; workbook.xlsm")
  
          If Sheets("Sheet1").Range("A1") = vbNullString Then
             Sheets("Sheet1").Range ("A1:N24")
             Selection.PasteSpecial Paste:=xlPasteFormats
             Selection.PasteSpecial Paste:=xlPasteValues
          Else
              Selection.Copy Sheets("sheet1").Cells(A1, Columns.Count).End(xlToLeft).Offset(0, 1)                   
          End If
          MyFile = Dir
      Loop
      End Sub
  </code></pre>