Private Sub Worksheet_Change(ByVal Target As Range)
Dim nbLigBatiment As Long, nbLigAjout As Long, lig As Long
Const lig1 As Long = 12 ' 1ère ligne des façades
'
If Target.Cells.Count > 1 Then Exit Sub
If Target <> [L5] Then Exit Sub
'
nbLigBatiment = Application.CountIf([A:A], "NORD")
nbLigAjout = [L5] - nbLigBatiment
' contrôle
If nbLigAjout < 1 Then
MsgBox ("On ne peut qu'ajouter des lignes")
Application.EnableEvents = False
[L5] = nbLigBatiment
Application.EnableEvents = True
Exit Sub
End If
' insérer les lignes
Application.Calculation = xlManual
For lig = lig1 + nbLigBatiment * 4 - 1 To lig1 Step -nbLigBatiment
Rows(lig).Copy
Rows(lig & ":" & lig + nbLigAjout - 1).Insert Shift:=xlDown
Next lig
Application.Calculation = xlAutomatic
Application.CutCopyMode = False
End Sub