Private Sub Worksheet_Activate()
On Error Resume Next 'si la colonne F est vide
Liens Range("F10:F" & Rows.Count).SpecialCells(xlCellTypeConstants)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Range("F10:N" & Rows.Count), Me.UsedRange)
If Not r Is Nothing Then Liens Intersect(r.EntireRow, [F:F])
End Sub
Sub Liens(r As Range)
Dim i As Variant, w As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'sécurité
For Each r In r
r(1, 8).Resize(, 2).Clear 'RAZ
If r <> "" Then
For Each w In Worksheets 'recherche dans toutes les feuilles
i = Application.Match(r, w.[A:A], 0)
If IsNumeric(i) Then
w.Cells(i, 2).Copy r(1, 8)
r(1, 9) = w.Name 'facultatif
Exit For
End If
Next w
End If
Next r
Application.EnableEvents = True 'réactive les évènements
End Sub