title: VBA Copy/Paste values with dynamic range into another Workbook, including dynamic file path lookup
body: <p>Lengthy title but hopefully I can explain. What I am wanting to do is Copy/Paste values from active Workbook into another Workbook contained in the same directory "folder" as the active. I need the range of the copy to be dynamic in the sense that if I add lines to the current range, I want the code to automatically pick this up and not have to manually amend the range in the code.</p>
  
  <p>I managed to find and slightly some code on StackOverflow which does what I need statically, but I have no idea how to add the various "dynamic" needs as my VBA knowledge is unfortunately not advanced enough. If you are able to assist, thanks very much in advance!</p>
  
  <p>Code:</p>
  
  <pre><code>Public Sub CopyData()
  Dim x As Workbook
  Dim y As Workbook
  Dim vals As Variant
  Dim RemoveRows
  </code></pre>
  
  <p><em>Below is where I need the "Path" of the new Workbook to be dynamic. I thought maybe along the lines of Application.ActiveWorkbook.Path to find the path, as I've seen that before, but I couldn't really get it to work. It then also needs to add the filename extension of the destination Workbook, which I have specified on "Sheet 1" in cell F2 of the Active Workbook. i.e. the destination Workbook to open would be "ActiveWorkbook.Path + Sheet 1 cell F2 + .xls" if that makes sense. The reason for cell F2 is so that I can use this code with other Workbook names.</em></p>
  
  <pre><code>'Open workbook first:
  Set x = Workbooks.Open("PATH")
  </code></pre>
  
  <p><em>Continue through code.</em></p>
  
  <pre><code>Set y = ThisWorkbook
  </code></pre>
  
  <p><em>Below Range is what I need to also be dynamic, ideally it would look in column A (starting in row 4), and create a copy/store range for all columns A-P until the first blank cell is located in A (i.e. exactly as per below, but the 13 should automatically increase to say 15 if I add 2 more rows with text in column A.</em></p>
  
  <pre><code>'Store values in a variable:
  vals = y.Sheets("Sheet1").Range("A4:P13").Value
  </code></pre>
  
  <p><em>Continue through code (paste/variable assign would need to be the exact same range as vals above except offset up by 2, i.e. should be A2 to P11 or P13 if rows added).</em></p>
  
  <pre><code>'Use the variable to assign a value to the other file/sheet:
  x.Sheets("Sheet2").Range("A2:P11").Value = vals
  
  'Delete Zero Rows
  Set x = ActiveWorkbook
  x.Sheets("Sheet2").Activate
  For RemoveRows = Range("K65536").End(xlUp).Row To 1 Step -1
     If Cells(RemoveRows, 11).Value = 0 Then Rows(RemoveRows).EntireRow.Delete
  
  Next RemoveRows
  
  'Copy Paste Values Again to Sheet 1 to ensure formatting uncompromised
  vals = x.Sheets("Sheet2").Range("A2:P1000").Value
  x.Sheets("Sheet1").Range("A2:P1000").Value = vals
  x.Sheets("Sheet2").Cells.Clear
  x.Sheets("Sheet1").Activate
  
  'Close x:
  If x.Saved = False Then
      x.Save
  End If
  x.Close
  
  End Sub
  </code></pre>