title: Creating dynamic range using xlup
body: <p>im trying to create a master file using vba to control various excel (dependants) files. Once the master file is open and running the macro, it will go through the directory, open the various excel files and copy paste the relevant columns to the master file. (i.e 3 dependant excel files (AA,BB,CC). i need to pull out column A to D in AA, C in BB and Z in CC.)</p>
  
  <p>My current code is able to do so but i have to have a static range established for the copy paste. Which is the following line: Range("A2:B6").copy. I am trying to render the range dynamic as such: Range ("A2: D &amp; last row with info). copy</p>
  
  <pre><code>Sub LoopThroughDirectory()
  Dim MyFile As String
  Dim erow
  Dim Filepath As String
  
  Filepath = "E:\Excel\"
  MyFile = Dir(Filepath)
  Do While Len(MyFile) &gt; 0
  
  
  If MyFile = "Name&amp;Age.xlsx" Then
  Workbooks.Open ("E:\Excel\Name&amp;Age")
  Range("A2:B6").Copy
  ActiveWorkbook.Close
  
  erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 2))
  End If
  
  If MyFile = "Food&amp;Quantity.xlsx" Then
  Workbooks.Open ("E:\Excel\Food&amp;Quantity")
  Range("D2:E6").Copy
  ActiveWorkbook.Close
  
  erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 3), Cells(erow, 4))
  End If
  
  MyFile = Dir
  Loop
  End Sub
  </code></pre>
  
  <p>I've tried to modified and use the following:</p>
  
  <pre><code>If MyFile = "Name&amp;Age.xlsx" Then
  Workbooks.Open ("E:\Excel\Name&amp;Age")
  erow1 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
  Range(Cells(A, 2), Cells(B, erow1)).Copy
  ActiveWorkbook.Close
  </code></pre>
  
  <p>But i keep getting error 1004. I think the problem lies in the following code: Range(Cells(A, 2), Cells(B, erow1)).Copy</p>
  
  <p>Any ideas?</p>
  
  <p>Hey simoco, thanks for your help, i tried code but it seems to take the row under last row with data (row which is empty). (for example, D2 to D6 has data, and E2 to E6 also). When using the following code, if i step into the code, i see that vba selects range:D7 to E all rows.</p>
  
  <p>here's the code: </p>
  
  <pre><code>If MyFile = "Food&amp;Quantity.xlsx" 
  Then Workbooks.Open ("E:\Excel\Food&amp;Quantity")
  erow3 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
  Sheet1.Range("D2:E" &amp; erow3).Copy
  ActiveWorkbook.Close
  
  erow4 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow4, 3), Cells(erow4, 4))
  End If
  </code></pre>
  
  <p>FOR Toni,</p>
  
  <p>Thanks for the input. The code works perfectly for static range. i do not receive any 1004 error. Even when copying and closing workbook before pasting it in another workbook. I did think of a "dumb" solution for it. I could use Range("A2:B1000000000000").copy but im wondering if there is a more "pretty" solution.
  Also, i did just try it with B100000000. it ran without error.</p>