title: Vba to reference a cell but remain static in the new cell
body: <p>My workbook has data that is automatically loaded in from yahoo finance. The data is constantly being refreshed. I need a formula that can take the referenced cell and produce the number value of that cell without updating whenever the referenced cell does. For example: Cell G3 will say 40.75 for the stock value and I need D2 to say 40.75 except that it will remain 40.75 when G3 updates to the new price.</p>
  
  <p>I have tried using <code>=Numbervalue($G$3)</code> but that still updates when the data refreshes.</p>
  
  <p><em>UPDATE</em></p>
  
  <p>This is the VBA Code that I have to paste the Date and Time in the active cell (Which is correct) then it will paste the price (G3) in the cell of D2. </p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub TimeStamp()
  '
  ' TimeStamp Macro
  '
  ' Keyboard Shortcut: Ctrl+Shift+T
  '
      ActiveCell.Formula = "=CONCATENATE(L1,N1)"
      ActiveCell.Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Application.CutCopyMode = False
  
      Range("D2").Value = Range("G3").Value
  End Sub
  </code></pre>
  
  <p>My problem is that I need the <code>Range("D2").Value = Range("G3").Value</code> part of the code to recognize the next empty cell in Column D. Currently, it will replace whatever data is already in the D2 cell.</p>