title: Copy from Multiple Excel Files (dynamic ranges) and Paste in master Sheet (Dynamic range)
body: <p>Can you please hep me with this code. I want to transfer data from multiple files to one master sheet. I am getting error in last but one line of the code</p>
  
  <pre><code>Sub LoopThroughDirectory()
  Dim MyFile As String
  Dim erow
  Dim Filepath As String
  Filepath = "C:\Users\Nadeem\Desktop\2013\2G\"
  MyFile = Dir(Filepath)
  Do While Len(MyFile) &gt; 0
  If MyFile = "zmaster.xlsm" Then      'zmaster is the master file in same folder
  Exit Sub
  End If
  
  Workbooks.Open (Filepath &amp; MyFile)
  Range("A2").Select
  Range(Selection.End(xlToRight), Selection.End(xlDown)).Copy   'Copy Dynamic range
  'ActiveWorkbook.Application.CutCopyMode = False
  'ActiveWorkbook.Close SaveChanges:=False
  
  Application.Workbooks("zmaster.xlsm").Worksheets("Sheet1").Activate 'Added this line
  erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row   'Paste in Dynamic Range
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1).Address)
  
  MyFile = Dir
  
  Loop
  End Sub
  </code></pre>