Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim F As Worksheet, a, n%, i As Variant, j As Variant
Set F = Sheets("DI par DR et par CF") 'feuille source
a = Array("IDF Nord", "BPL") 'liste des noms des feuilles à traiter
Application.EnableEvents = False
On Error Resume Next 'sécurité
If Sh.Name = F.Name Then
Application.ScreenUpdating = False
For n = 0 To UBound(a)
With Sheets(a(n))
.ShowAllData 'si la feuille est filtrée
.Rows("9:" & .Rows.Count) = "" 'RAZ
i = Application.Match(.[A8], Sh.[A:A], 0)
If IsNumeric(i) Then
j = Application.Match("*", Sh.Range("A" & i + 1 & ":A" & Sh.Rows.Count), 0)
If IsError(j) Then j = Sh.[B:B].Find("", Sh.Cells(i, 2), xlValues).Row - i
.[A9].Resize(j, 7) = Sh.Cells(i, 2).Resize(j, 7).Value
.[I8:K8].AutoFill .[I8:K8].Resize(j + 1), xlFillValues
End If
End With
Next
ElseIf IsNumeric(Application.Match(Sh.Name, a, 0)) Then
If Not Intersect(Target, [A8]) Is Nothing Then Workbook_SheetChange F, F.[A1] 'MAJ
If Not Intersect(Target, Sh.Rows("9:" & Sh.Rows.Count)) Is Nothing Then Application.Undo 'annule la modification manuelle
End If
Application.EnableEvents = True
End Sub