title: Inserting a formula containing variable into a cell using a macro
body: <p>I have a vba macro that copies the current sheet and renames it with the current date that works fine, now I want it to put a formula in cell B22, the formula that I want is:</p>
  
  <pre><code>  Current Sheet       Previous Sheet
  ='08 May 2013'!B18 - '01-04-2013'!B18
  </code></pre>
  
  <p>I have been trying to get this code to work, but it is putting it in as a string not a formula
   i.e.   = ('[08 May 2013 !R2C18] - [  12 May 2013 !R2C18])</p>
  
  <pre><code>Dim sheet_name As String
  Dim activeSheet_name As String
  Dim shtName As Integer
  Dim activeShtName As Integer
  shtName = Sheets.Count - 1
  activeShtName = Sheets.Count
  sheet_name = Sheets(shtName).Name
  activeSheet_name = Sheets(activeShtName).Name
  Sheets(activeSheet_name).Select
  Range("B22").Select
  ActiveCell.Formula = " = ('[" &amp; sheet_name &amp; " !R2C18] - [  " &amp; activeSheet_name &amp; " !R2C18])"
  </code></pre>
  
  <p>Any help would be appreciated </p>