Excel VBA code for hyperlink to other sheets

Question

The workbook I am working on is used for project tracking. On the opening page it is the current&upcoming projects page. When you press a Form Control Button it executes the code below. What the code below does is reads through each sheet in the workbook(has 30 sheets), and then all the sheets that have value "Project # :" in A5. When it has that value it will put specified values into the specified row and column. The line with "**" at the beginning is the one that doesn't work. The line below it with "*" next to it is what I temporarily use, but that has no hyperlink, just the sheets name.

What I want to happen in the code below is make Row "A" contain the sheets names as text and hyperlink to that sheet

 For Each ws In ThisWorkbook.Worksheets
            If ws.Range("A5") = "Project # :" And ws.Range("E16") = "" Then
                x = .Range("B" & Rows.Count).End(xlUp).Offset(1).row

                **.Cells(x, "A").Formula = "=ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _'" & ws.Name & "'"
                *.Cells(x, "A").Value = ws.Name    'classifying number

                .Cells(x, "B").Formula = "='" & ws.Name & "'!$B$5"    'Project #
                .Cells(x, "C").Formula = "='" & ws.Name & "'!$A$1"    'Project Name
                .Cells(x, "D").Formula = "='" & ws.Name & "'!$B$8"    'Project Engineer
                .Cells(x, "E").Formula = "='" & ws.Name & "'!$B$11"    'In-service Due
                .Cells(x, "F").Formula = "='" & ws.Name & "'!$E$6"    '30% Due
                .Cells(x, "G").Formula = "='" & ws.Name & "'!$F$13"    '30% Success
                .Cells(x, "H").Formula = "='" & ws.Name & "'!$E$7"    '60% due
                .Cells(x, "I").Formula = "='" & ws.Name & "'!$F$14"    '60% Success
                .Cells(x, "J").Formula = "='" & ws.Name & "'!$E$8"    '90% due
                .Cells(x, "K").Formula = "='" & ws.Name & "'!$F$15"    '90% Success
                .Cells(x, "L").Formula = "='" & ws.Name & "'!$E$5"    'Material Forecast due date
                .Cells(x, "M").Formula = "='" & ws.Name & "'!$F$11"    'Materials Forecast Success
                .Cells(x, "N").Formula = "='" & ws.Name & "'!$B$15"    'Non Stores Items
                .Cells(x, "O").Formula = "='" & ws.Name & "'!$B$16"    'Non Stores Items Ordered on time
                .Cells(x, "P").Formula = "='" & ws.Name & "'!$A$17"    'Non Stores Items Success
            End If
        Next

    End With

YowE3K YowE3K · Accepted Answer · 2016-08-30T20:31:12

Instead of

.Cells(x, "A").Formula = "=ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _'" & ws.Name & "'"

use

.Hyperlinks.Add Anchor:=.Cells(x, "A"), _
                Address:="", _
                SubAddress:= "'" & ws.Name & "'!A1", _
                TextToDisplay:= ws.Name

I have assumed that your code is being executed within a With ActiveSheet (or equivalent) block.

Excel VBA code for hyperlink to other sheets

1 Answers