Auto create Hyperlink to Sheet within work book-CodePudding

Hope you can help.

I have an massive work book with 60 work sheet but growing every day, on the control sheet column A is the Job number, this is also the name of the Worksheet, is there a way I can create a Macro to automatically add a hyperlink from the cell containing the job number to the worksheet for that job (with the same name) i have attached a very cut down version of the workbook

I have managed to get all worksheets listed on Sheet1, however this only works by removing the working links that are there re adding new ones and it adds all worksheets in the workbook not just those listed on the control sheet.

this is working from all the worksheets to a list I need it to work from the list and find the worksheet

thanks in advance

Sub ListSheets()

Dim ws As Worksheet
Dim x As Integer

x = 1

Sheets("Control").Range("A:A").Clear

For Each ws In Worksheets

   Sheets("Control").Cells(x, 1).Select
   ActiveSheet.Hyperlinks.Add _
   Anchor:=Selection, Address:="", SubAddress:= _
   ws.Name & "!A2", TextToDisplay:=ws.Name
   x = x   1

Next ws

End Sub

CodePudding user response：

If you have a list of all the worksheets, there is no need to loop through the worksheet objects, just use the list entries. If you need to allow for missing worksheets, include an WorksheetExists function to check if the sheet is there.

Sub ListSheets()
    Dim lastrow As Long, rw As Long
    With Worksheets("Control")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For rw = 2 To lastrow
            If WorksheetExists(.Cells(rw, 1).Value) Then
                .Cells(rw, 1).Hyperlinks.Add _
                   Anchor:=.Cells(rw, 1), Address:="", SubAddress:= _
                   .Cells(rw, 1).Value & "!A2", TextToDisplay:=.Cells(rw, 1).Value
            End If
        Next
    End With
End Sub

And the WorksheetExists function from Tim Williams answer: https://stackoverflow.com/a/6688482/7446760

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function