title: Excel VBA macro. How to write an absolute cell reference into a cell
body: <p>I have a workbook with an index as the first sheet. Each subsequent sheet is a dive-log. As I am one of many using this workbook, it needs to be as 'automatic' as possible (because people are lazy)... </p>
  
  <p>Each log has a macro button for 'New Dive'. The macro creates a new sheet, names it with the new sheet number (the dive number) and clears relevant data ready for filling in. Currently the index sheet needs to be filled manually, but it's being ignored.</p>
  
  <p>I have the macro close, but it's the last step that has me stumped. I've tried <code>Activecell.FormulaR1C1 and Cells(r,c) =...</code> gets close, but no piece of pie. I'm also VERY new at this.</p>
  
  <p>Here's my code</p>
  
  <pre><code>Sub Add_links()
  '
  ' Add_links Macro
  ' Adds links to the index sheet so it 'fills itself in'... 
  
  ' Each dive is on the row 9 more than the dive number (bear in mind each log is 50 higher than the previous)
  
  Dim Divenumber As Double
  Dim Rownumber As Double
  
  Range("I7").Select: Divenumber = ActiveCell.FormulaR1C1
  
  ' Make Linenumber the same as Divenumber.
  ' Do a loop of reducing the Linenumber by 50 until it's in the range 1 to 50.
  ' Add 9 to that and it becomes the row number of the index sheet
  
  Rownumber = Divenumber
  
  Do
  Rownumber = Rownumber - 50
  Loop While Rownumber &gt; 50
  
  Rownumber = Rownumber + 9
  
  Worksheets("Dive Index").Activate
  
  Range("A" &amp; Rownumber).Select
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
      "'Dive " &amp; Divenumber &amp; "'!A1"
  'Project number (in cell F4)
  Range("B" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; "'!F4"
  'Task(in cell C7)
  Range("C" &amp; Rownumber &amp; ":G" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; "'!C7"
  'Start date (in cell C21)
  Range("H" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; Chr(39) &amp; "!$C$21"
  'Start time (in cell E21)
  Range("I" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; Chr(39) &amp; "!$E$21"
  'End date (in cell F21)
  Range("J" &amp; Rownumber &amp; ":K" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; Chr(39) &amp; "!$F$21"
  'End time (in cell G21)
  Range("L" &amp; Rownumber).Select
  ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; Chr(39) &amp; "!$G$21"
  
  Sheets("Dive " &amp; Divenumber).Select
  Range("A23").Select
  End Sub
  </code></pre>
  
  <p>This one gets me the closest..</p>
  
  <pre><code>ActiveCell.FormulaR1C1 = "='Dive " &amp; Divenumber &amp; "'!F4"
  </code></pre>
  
  <p>but adds a couple of unwanted ' to the cell.. Looks like this...</p>
  
  <p><code>='Dive 53'!'F4'</code> (should be <code>='Dive 53'!F4</code>)</p>