To Summarize the sheet with hyperlink

[mayaa_mmm]

I need to summarize the sheet with sheet name as " Summary sheet" with hyperlink.

In the below code , it is summarize but file name is not proper and i need to do manually the hyperlink.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


// linking the sheets


=HYPERLINK("#'"&A2&"'!A1",A2)

Thank you in advance

 

[ParamRay]

.
.

Try something like this:

Private Sub AddSummarySheet()
    
    Dim Sh As Worksheet
    Dim WS As Worksheet
    Dim Rw As Byte
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    On Error Resume Next
    ActiveWorkbook.Sheets("Summary Sheet").Delete
    On Error GoTo 0
    
    Set WS = ActiveWorkbook.Worksheets.Add(Before:=Sheets(1))
    WS.Name = "Summary Sheet"
    
    Rw = 1
    For Each Sh In ActiveWorkbook.Worksheets
        If Not Sh Is WS Then
            Sh.Hyperlinks.Add _
                Anchor:=WS.Cells(Rw, "A"), _
                Address:=vbNullString, _
                SubAddress:=Sh.Name & "!A1", _
                TextToDisplay:=Sh.Name
            Rw = Rw + 1
        End If
    Next Sh

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub