title: Excel VBA Macro: Copying cells by reference
body: <p>I am creating a macro in Excel to copy data from one sheet to another within the same workbook.  Currently, the code operates as so:</p>
  
  <pre><code>Dim sourceSheet As Worksheet
  Dim destSheet As Worksheet
  Dim numCases As Integer
  Dim i As Integer
  
  numCases = 10
  Set destSheet = ThisWorkbook.Sheets("Raw_Data")
  Set sourceSheet = ThisWorkbook.Sheets("Formatted_Data")
  
  For i = 1 To numCases
      destSheet.Cells(i,1).Value = sourceSheet.Cells(1,i).Value
  Next
  </code></pre>
  
  <p>This code works properly in that it copies the value from one sheet to the other and places it in the correct cell.  The issue is that I need the cell to be populated as a reference value back to sheet "Raw_Data".  In other words, when I select a cell in "Formatted_Data", I need the formula bar to show something like:
  'Raw_Data'!C1
  but instead all I get is the numerical value.  Is there some way that I can perform a copy, or set a value and maintain the reference??</p>
  
  <p>Thanks,</p>
  
  <p>Barret</p>