Make a liste of all sheets in one sheet in specific column and hyperlink it with the resoective sheet.

Hi @Chaturvedi, Santosh,

Thank you for posting your question in the Microsoft Q&A forum.

From your description, you’d like to generate a list of all worksheets in your workbook, starting in column D (cell D4 onwards) of the sheet "Guage_Liste_DB", with each entry hyperlinked to its respective sheet. For this scenario, please try the following macro:

Sub ExtractSheetsWithHyperlinks()

    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim rowNum As Long
    Dim sheetName As String

    ' Speed up the macro execution
    Application.ScreenUpdating = False

    ' Set the target sheet explicitly
    Set targetSheet = ThisWorkbook.Worksheets("Guage_Liste_DB")

    ' Start writing links at row 4, column D
    rowNum = 4

    ' Loop through every sheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the target sheet itself
        If ws.Name <> targetSheet.Name Then
            sheetName = ws.Name

            ' Create the hyperlink targeting cell A1 of each sheet
            targetSheet.Hyperlinks.Add _
                Anchor:=targetSheet.Cells(rowNum, 4), _
                Address:="", _
                SubAddress:="'" & sheetName & "'!A1", _
                TextToDisplay:=sheetName

            rowNum = rowNum + 1
        End If
    Next ws

    ' Auto-fit column D so names aren't clipped
    targetSheet.Columns("D").AutoFit

    ' Restore screen updating
    Application.ScreenUpdating = True

    ' Completion notification
    MsgBox "Generated " & (rowNum - 4) & " hyperlinks successfully!", vbInformation, "Done"

End Sub

I tested this with a workbook containing 30 sheets.

After running the macro, the sheet Guage_Liste_DB displayed a clean list of all sheet names in column D starting at row 4, each with a clickable hyperlink that navigates directly to the corresponding sheet.

Thank you again for your time and understanding. I really appreciate your patience, and I’m here to help. Looking forward to your response!                      

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".                 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread