title: create hyperlink to new cell automatically
body: <p>I have a code that creates a hyperlink to a new worksheet that is created, in another worksheet called 'Management', in cell 'A6'. How can I code it so that every time I create a new sheet, the hyperlink is created to different cell? For example, A7, A8, A9 etc for each new sheet is created. </p>
  
  <p>Here is the code I have so far</p>
  
  <pre><code>Private Sub Button8_Click()
  
  Dim newSheet As Worksheet
  Dim newName As String
  
  Do
  newName = Application.InputBox("What do you want to name the new sheet?", Type:=2)
  If newName = "False" Then Exit Sub: Rem cancel pressed
  
  Set newSheet = ThisWorkbook.Sheets.Add
  
  On Error Resume Next
      newSheet.Name = newName
      newName = Error
  On Error GoTo 0
  
  If newName &lt;&gt; vbNullString Then
      Application.DisplayAlerts = False
          newSheet.Delete
      Application.DisplayAlerts = True
      MsgBox newName
  End If
  
  Loop Until newName = vbNullString
  
  ThisWorkbook.Worksheets("Version Checklist").Cells.Copy
  newSheet.Paste
  
  Dim targetSheet As Worksheet
  Dim targetRange As Range
  Dim linkedSheet As Worksheet
  Dim linkRange As Range
  
  'set variable to the sheet the hyperlink will link to
  Set targetSheet = ThisWorkbook.Sheets(ActiveSheet.Name)
  
  ' specify the range on the summary sheet to link to
  Set targetRange = targetSheet.Range("A1:Z100")
  
  ' set variable to sheet that will have the hyperlink
  Set linkedSheet = ThisWorkbook.Sheets("Management")
  
  ' specify where on that sheet we'll create the hyperlink
  Set linkRange = linkedSheet.Range("A6")
  
  ' create the hypperlink on the copied sheet pointing
  ' back to the summary sheet
  linkedSheet.Hyperlinks.Add Anchor:=linkRange, Address:="", SubAddress:= _
      "'" &amp; targetSheet.Name &amp; "'!" &amp; targetRange.Address, _
      TextToDisplay:=targetSheet.Name
  
  End sub
  </code></pre>