title: VBA hyperlink on all cell when creating a range
body: <p>I would like to apply <code>=HYPERLINK()</code> function in Excel on the "XYZ" range where each cell is referenced to its counterpart in ABC sheet. Just not sure how to do that as I am a toal noob when it comes to VBA. </p>
  
  <pre><code>Sub Calculator()
      Dim instances As Long
      Dim blanks As Long
  
      ' Prepare for allocation
      Set mainline = ActiveWorkbook
      lastrowABC = Sheets("ABC").Cells(Rows.Count, 1).End(xlUp).Row
  
      'newprep
      Sheets("Sheet").Cells.Clear
      Application.Calculation = xlAutomatic
      Application.ScreenUpdating = False
  
  
      ' Add Formulas to Sheet Tab
      Worksheets("Sheet").Activate
      With Sheets("Sheet")
              .Range("A1") = "XYZ"
              .Range("A2:A" &amp; lastrowABC) = Sheets("ABC").Range("Z2:Z" &amp; lastrowABC).value
  </code></pre>