title: Paste To Active Cell Using Range
body: <p>Any help would be greatly received.</p>
  
  <p>I'm trying to write vba code that copies a range of cells from one workbook to another. The destination file is active and contains a date in column A; this is used to open the source file (file name contains a date), cells are then copied and paste into the destination file from the source file. The code works perfectly if i specify the destination range manually as follows:</p>
  
  <pre><code>x.Sheets("Sheet1").Range("B4:D4").Copy Destination:=y.Sheets("Sheet1").**Range("B5")**
  </code></pre>
  
  <p>However; i would like the destination range to be dynamic.... i would like it to be the same as the active cell. I have amended this in various ways but still cannot find a solution:</p>
  
  <pre><code>Sub OpenWb()
  
  Dim FileDate As Date
  Dim x As Workbook
  Dim y As Workbook
  
  Application.ScreenUpdating = False
  
  FileDate = ActiveCell.Offset(0, -1)
  
  
  'Define x and open y
  
  Set y = ActiveWorkbook
  
  Set x = Workbooks.Open("C:\Users\yatesa\Documents\" &amp; Format(FileDate, "dd-mm-yy") &amp; " Production Data.xlsx")
  
  'Transfers values from x to y
  
  x.Sheets("Sheet1").Range("B4:D4").Copy Destination:=y.Sheets("Sheet1").**Range(ActiveCell)**
  
  'Close x
  
  x.Close
  
  End Sub
  </code></pre>
  
  <p>Many Thanks.</p>