title: vba insert formula and get dynamic cell reference (cell reference of current row)?
body: <p>I am using the following code to copy data from one sheet to another, this is inserted into a new row each time new data is copied. I want to be able to insert a formula into the cells D and E of the active/current row and use a dynamic cell reference. so if row 5 it will be A5 and if row 6 it will be A6 and so on.</p>
  
  <p>I have tried using ActiveCell.Formula and DestRow and nothing works. </p>
  
  <p>Can someone please show me how I can make this happen? Thanks in advance    </p>
  
  <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Application.DisplayAlerts = False
      If Target.Column = Range("Z1").Column And Range("Z" &amp; ActiveCell.Row).Value = "SUBMIT" Then
          Dim ws1 As Worksheet, ws2 As Worksheet
          Dim DestRow As Long
          Set ws1 = Sheets("Home")
          Set ws2 = Sheets("Statistics")
          DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
          ws1.Range("B10").Copy
          ws2.Range("A" &amp; DestRow).PasteSpecial xlPasteValuesAndNumberFormats
          ws1.Range("B15").Copy
          ws2.Range("B" &amp; DestRow).PasteSpecial xlPasteValuesAndNumberFormats
          ws1.Range("B20").Copy
          ws2.Range("C" &amp; DestRow).PasteSpecial xlPasteValuesAndNumberFormats
          ws2.Range("D" &amp; DestRow).Formula = "=IF(ISTEXT(A5),IF(E5 &lt;&gt;""Yes"",CONCATENATE(""NS"")&amp;RANDBETWEEN(0,9)&amp;RANDBETWEEN(0,9)&amp;RANDBETWEEN(0,9)&amp;RANDBETWEEN(0,9)&amp;RANDBETWEEN(0,9)&amp;RANDBETWEEN(0,9),D5),"""")"
          ws2.Range("E" &amp; DestRow).Formula = "=IF(ISTEXT(A5),IF(ISTEXT(D5),""Yes"",""N/A""),"""")"
          ws1.Range("Y7").Copy
          ws2.Range("F" &amp; DestRow).PasteSpecial xlPasteValuesAndNumberFormats
          ws1.Range("H10").Copy
          ws2.Range("H" &amp; DestRow).PasteSpecial xlPasteValuesAndNumberFormats
      End If
      End Sub
  </code></pre>