Private Sub Worksheet_Activate()
Worksheets("Sheet1").Range("B5:B9999").Clear
   Dim sheet As Worksheet
   Dim cell As Range
   With ActiveWorkbook
      For Each sheet In ActiveWorkbook.Worksheets
         Set cell = Worksheets(1).Cells(sheet.Index + 4, 2)
         .Worksheets(7).Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!B5"
         cell.Formula = sheet.Name
         
      Next
   End With
End Sub