Creating hyperlink for specific sheets while creating those sheets

DCEXC

New Member
Joined
Oct 28, 2019
Messages
14
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,707
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:
VBA Code:
    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

New Member
Joined
Oct 28, 2019
Messages
14
Th
Untested, maybe:
VBA Code:
    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

New Member
Joined
Oct 28, 2019
Messages
14
have fixed it, got it to apply all hyperlinks to template, then during sheet creation loop to remove the hyperlink that linked to itself
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top