Try This code
Option Explicit
Sub ww()
Dim h As Worksheet, sh As Worksheet
Dim cnt%
Set h = Sheets("sheet1")
h.Range("B2").Resize(Sheets.Count - 1).ClearContents
cnt = 2
For Each sh In Sheets
If Not sh.Name = "Sheet1" Then
h.Hyperlinks.Add Anchor:=h.Cells(cnt, 2), _
Address:="", SubAddress:=sh.Name & "!A1", _
TextToDisplay:="Go to " & sh.Name
h.Cells(cnt, 2).Font.Size = 16
sh.Cells(1, 6).ClearContents
sh.Hyperlinks.Add Anchor:=sh.Cells(1, 6), _
Address:="", SubAddress:=h.Name & "!A1", _
TextToDisplay:="Go to " & h.Name
sh.Cells(1, 5) = h.Cells(cnt, 3)
cnt = cnt + 1
End If
Next
End Sub