Snasui Excel Forum

Code: Select all

Private Sub CommandButton2_Click()

Do
Dim b As String
r = r + 1
b = TextBox1.Text
Loop Until Cells(r, 1) = ""
Cells(r, 1) = b
Sheets("template").Copy After:=Worksheets("sheet1")
ActiveSheet.Name = b

 'Sheets("sheet1").Cells(r, 1).Select
  ' ActiveSheet.Hyperlinks.add _
   'Anchor:=Selection, Address:="", SubAddress:= _
  ' b.Name & "!A1", TextToDisplay:=b.Name
Unload Me
End Sub

Code: Select all

Private Sub CommandButton2_Click()
    Dim b As String
    Do
        r = r + 1
        b = TextBox1.Text
    Loop Until Cells(r, 1) = ""
    Cells(r, 1) = b
    Sheets("template").Copy After:=Worksheets("sheet1")
    ActiveSheet.Name = b
    
    With Sheets("sheet1")
        .Hyperlinks.add Anchor:=.Cells(r, 2), Address:="", SubAddress:= _
        "#'" & b & "'!A1", TextToDisplay:=b
    End With
    Unload Me
End Sub