[Create A Hyperlink to a sheet based on Cell Value] Add Hyperlinked Page #VBA #Excel

addhyperlinkedsheet.bas

Sub createNewStepsWorksheet()

Dim totalSheets As Integer
Dim sheetName As String

sheetName = ActiveCell.Value

With ActiveWorkbook
    totalSheets = .Worksheets.Count
    .Sheets(2).Copy After:=Sheets(totalSheets)
End With

ActiveSheet.Name = sheetName

End Sub

Sub addHyperlinkedSheet()
'test to see whether sheet exists. If it does not, create sheet first
Dim sheetIndex As Long
Dim decision As Integer
Dim sheetReference As String
Dim anchorRng As Range

sheetReference = ActiveCell.Value
Set anchorRng = ActiveCell

On Error GoTo InvalidCell
sheetIndex = Sheets(ActiveCell.Value).Index

Call addHyperlinktoNamedSheet2(sheetIndex, sheetReference, anchorRng)

Exit Sub


InvalidCell:
decision = MsgBox("I couldn't find a worksheet with that name!" _
            & vbLf & "Do you want me to create it?", vbYesNo)
If decision = vbYes Then
    Call createNewStepsWorksheet
    Call addHyperlinktoNamedSheet2(sheetIndex, sheetReference, anchorRng)
End If
On Error GoTo 0
End Sub

Sub addHyperlinktoNamedSheet2(sheetIndex As Long, sheetRef As String, anchorRng As Range)
' access by Ctrl + h

Dim hyperlinkAddress As String


hyperlinkAddress = "'" & sheetRef & "'!A1"

With ActiveSheet
    .Hyperlinks.Add Anchor:=anchorRng, Address:="", _
        SubAddress:=hyperlinkAddress, TextToDisplay:=sheetRef
     
End With

End Sub

Add hyperlinked sheet is designed to link to a sheet based on the text inside a cell. If the named sheet cannot be found, the macro will create the sheet. At the moment, the sheet which is copied is hard coded as Sheet2. This could easily be converted to being a parameter to this function