Renaming sheets with hyperlink-HELP! :[

[amateur717]

Hi,

I am trying to create a macro that will enable me to create new sheets within a workbook, naming the new sheet with something in a certain cell, then hyperlinking that cell to the the sheet.

Please help!

 

[VoG]

With a list of sheet names in Sheet1 column A starting in A1 try

Sub AddSheets()
Dim LR As Long, i As Long, ws As Worksheet
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        ws.Name = .Range("A" & i).Value
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    Next i
End With
End Sub

 

[amateur717]

Awesome!!! Thank you so much!

-Now, how can i add onto that to make it so that each Sheet has the same template as a 'Template' sheet? i.e. copy an existing sheet, then renaming it with the contents of the cell, and hyperlinking that cell to the sheet?

This should be all i need to do.

 

[VoG]

Try like this

Sub AddSheets()
Dim LR As Long, i As Long, ws As Worksheet
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Worksheets("Template").Copy after:=Worksheets(Worksheets.Count)
        Set ws = ActiveSheet
        ws.Name = .Range("A" & i).Value
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    Next i
End With
End Sub