Sommaire dynamique qui Pointe vers des cellules du classeur

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If UCase(Target) = "VALIDE" And ActiveSheet.Name <> "Sommaire" Then
With Sheets("Sommaire")
Derlign = .Range("A65000").End(xlUp).Row + 1
.Hyperlinks.Add Anchor:=.Range("A" & Derlign), Address:="", SubAddress:="" & ActiveSheet.Name & "!" & Target.Offset(0, 1).Address, _
TextToDisplay:=[COLOR="Red"]Activesheet.Name & ":" [/COLOR]& Target.Offset(0, 1).Value
End With
End If
End Sub

Private Sub Worksheet_Activate()
    Range("C6").Select
    Range(ActiveCell, [C65000].End(xlUp)).ClearContents
    For i = 2 To Sheets.Count
        nf = Sheets(i).Name
        Set c = Sheets(i).Cells.Find(What:="valide", LookIn:=xlValues)
        If Not c Is Nothing Then
          premier = c.Address
          Do
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & _
              nf & "'!" & c.Address, TextToDisplay:=nf & " :" & c.Address
            ActiveCell.Offset(1, 0).Select
            Set c = Sheets(i).Cells.FindNext(c)
          Loop While Not c Is Nothing And c.Address <> premier
        End If
    Next i
End Sub

Private Sub Worksheet_Activate()
    Range("C6").Select
    Range(ActiveCell, [C65000].End(xlUp)).ClearContents
    For i = 2 To Sheets.Count
      If Sheets(i).CheckBox1 = True Then
        nf = Sheets(i).Name
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & _
              nf & "'!A1", TextToDisplay:=nf
        ActiveCell.Offset(1, 0).Select
     End If
    Next i
End Sub