Creating hyperlink for specific sheets while creating those sheets

[DCEXC]

i wrote a code that will create as many worksheets and name them for each cell value in a named range. my code gets my template sheet, and duplicates it and renames each one accordingly.

on my template sheet, i have a navigation pane in column A, so from A17 downwards, id like to create hyperlinks to each new sheets on each new sheet being created. if that makes sense..probably not.

i have gotten the code to work, but it only puts the hyperlinks on the last sheet, basically my thought was once it has finished with the "creating sheets loop", for it to loop through those new sheets and add in all the hyperlinks.

AND yes - most likely i have done this the LONGEST way possible, the way i thought it was the way i wrote it - BUT im super new to coding/ VBA and im doing this for work to make my life 1000000 times easier. any help would be amazing.

codes below:

Private Sub GenerateInputSheetsButton_Click()

'On Error GoTo err
Application.ScreenUpdating = False
InfoSheet.Visible = True
InfoSheet.Select



With ActiveWorkbook.ActiveSheet

Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("Info")
Set sh = Sheets("Input Template")
Dim rng As Integer
Dim cell As Range

rng = InfoSheet.Range(("O1"), Range("O1").End(xlDown)).Count
Application.ScreenUpdating = 0

    For i = 1 To rng
   
        Sheets("Input Template").Copy Before:=sh
        ActiveSheet.Name = ws.Range("O" & i).Value
        ActiveSheet.Range("b2").Value = ws.Range("O" & i).Value
    Next i
   
    For i = 1 To rng
    Call CreateHyperLinks
    Next i
    End With
   
Sheets("Input Template").Visible = False
HomeSheet.Select

Application.ScreenUpdating = True
'err: Exit Sub


End Sub

Sub CreateHyperLinks()

    Dim ws As Worksheet
    Dim x As Integer
   
        x = 17
       
        For Each ws In Worksheets
       
            If ws.Visible = xlSheetVisible Then
            If ws.Name <> HomeSheet.Name Then
            If ws.Name <> InfoSheet.Name Then
            If ws.Name <> TermLibrarySheet.Name Then
            If ws.Name <> InputSheet.Name Then
            If ws.Name <> TermLibrarySheet.Name Then
            If ws.Name <> QuotePreviewSheet.Name Then
            If ws.Name <> MaterialSummarySheet.Name Then
            If ActiveSheet.Name <> ws.Name Then
           
                ActiveSheet.Cells(x, 1).Select
                ActiveSheet.Hyperlinks.Add _
                Anchor:=Selection, Address:="", SubAddress:= _
                ws.Name & "!A1", TextToDisplay:=ws.Name
                x = x + 2
               
                With Selection.Font
                    .Name = "Stylus BT"
                    .Size = 12
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleSingle
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeColorDark1
                    .Bold = True
                End With
           
            End If
            End If
            End If
            End If
            End If
            End If
            End If
            End If
            End If
           
        Next ws

End Sub

 

[John_w]

on my template sheet, i have a navigation pane in column A, so from A17 downwards, id like to create hyperlinks to each new sheets on each new sheet being created.

Untested, maybe:

    For i = 1 To Rng
        sh.Copy Before:=sh
        ActiveSheet.Name = ws.Range("O" & i).Value
        ActiveSheet.Range("b2").Value = ws.Range("O" & i).Value
        sh.Hyperlinks.Add Anchor:=sh.Range("A17").Offset(i - 1), Address:="", SubAddress:=ActiveSheet.Name & "!A1", TextToDisplay:=ActiveSheet.Name
    Next i

and no need for Sub CreateHyperLinks()

 

[DCEXC]

Th

Untested, maybe:

    For i = 1 To Rng
        sh.Copy Before:=sh
        ActiveSheet.Name = ws.Range("O" & i).Value
        ActiveSheet.Range("b2").Value = ws.Range("O" & i).Value
        sh.Hyperlinks.Add Anchor:=sh.Range("A17").Offset(i - 1), Address:="", SubAddress:=ActiveSheet.Name & "!A1", TextToDisplay:=ActiveSheet.Name
    Next i

and no need for Sub CreateHyperLinks()

Thanks for the reply John!
so so close! except:
it was creating 4 sheets, (info sheet is where it was getting the names from)
the sheets were for example - sheet 1, 2 , 3 & 4

On the first it had no links
2nd it had a link to the first
3rd it had links to 1st and 2nd
4th had links to 1,2,3
then i made template sheet visible and it had links to all 4

 

[DCEXC]

have fixed it, got it to apply all hyperlinks to template, then during sheet creation loop to remove the hyperlink that linked to itself