title: Programmatically creating a hyperlink to a worksheet
body: <p>I have been working on this for a while, I don't really know how to use vba, and I've been looking for a solution on the web for a while without success. Here is what I want to do:</p>
  
  <p>I have a macro that will copy a worksheet (named "PartSheet") and put it at the end of the list of worksheets, so if I keep pressing the macro it will create pages as follow:</p>
  
  <p>PartSheet (2), PartSheet (3) for the tab name and so on.</p>
  
  <p>Now I am trying to add in a hyperlink into this macro so that each time I create a new copy
  it will also create a hyperlink to the first worksheet (named "Summary")</p>
  
  <p>Here is the code I have written:</p>
  
  <pre><code>Sub Test2()
      Sheets(Sheets.Count).Select
      Name = ActiveSheet.Name
      Sheets("Summary").Select
      Range("A10").Select
      ActiveCell.FormulaR1C1 = ""
      Range("A10").Select
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
          " 'Name' !A1 ", TextToDisplay:="Link"
  End Sub
  </code></pre>
  
  <p>When I run the macro, it will create the hyperlink, but it will be a invalid reference.</p>