XL 2010 FAIRE LISTE AUTOMATIQUE SUIVANT NOMBRES

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Round([C9], 0) <> [C11] Or Intersect(Target, [D7:AD7,C11]) Is Nothing Then Exit Sub
With [B:B].Find("", [B11], xlValues) '1ère cellule vide sous B11
    With .Resize(, 29)
        .Value = [B9:AD9].Value
        .Borders(xlEdgeBottom).Weight = xlMedium
    End With
    .Value = "PF " & .Row - [B11].Row
End With
End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Total As Double, MaxPF As Double, NbLig As Long, RngLig As Range
   If Intersect(Target, [D7:AD7,C11,D12:AD1000], Target) Is Nothing Then Exit Sub
   Total = [C9].Value
   MaxPF = [C11].Value
   NbLig = Int(Total / MaxPF): If NbLig * MaxPF < Total Then NbLig = NbLig + 1
   Set RngLig = [B12:AD12].Resize(NbLig)
   Application.EnableEvents = False
   With RngLig.Offset(NbLig).Resize(1000)
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
      .ClearContents: End With
   With RngLig.Borders(xlEdgeBottom): .LineStyle = xlContinuous: .Weight = xlMedium: End With
   With RngLig.Borders(xlInsideHorizontal): .LineStyle = xlContinuous: .Weight = xlThin: End With
   RngLig.Columns(2).Value = MaxPF
   RngLig.Rows(RngLig.Rows.Count).FormulaR1C1 = "=R9C-SUM(R12C:R[-1]C)"
   RngLig.Columns(1).FormulaR1C1 = "=""PF ""&ROW()-" & RngLig.Row - 1
   RngLig.Value = RngLig.Value
   Application.EnableEvents = True
   End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Total As Double, MaxPF As Double, NbLig As Long, RngLig As Range
   If Intersect(Target, [D7:AD7,C11,D12:AD1000], Target) Is Nothing Then Exit Sub
   Total = [C9].Value
   MaxPF = [C11].Value
   NbLig = Int(Total / MaxPF): If NbLig * MaxPF < Total Then NbLig = NbLig + 1
   If NbLig = 0 Then NbLig = 1
   Set RngLig = [B12:AD12].Resize(NbLig)
   Application.EnableEvents = False
   With RngLig.Offset(NbLig).Resize(1000)
      .Borders(xlEdgeBottom).LineStyle = xlNone
      .Borders(xlInsideHorizontal).LineStyle = xlNone
      .ClearContents: End With
   With RngLig.Borders(xlEdgeBottom): .LineStyle = xlContinuous: .Weight = xlMedium: End With
   With RngLig.Borders(xlInsideHorizontal): .LineStyle = xlContinuous: .Weight = xlThin: End With
   RngLig.Columns(2).Value = MaxPF
   If RngLig.Rows.Count > 1 Then
      RngLig.Rows(RngLig.Rows.Count).FormulaR1C1 = "=R9C-SUM(R12C:R[-1]C)"
   Else
      RngLig.Rows(1).FormulaR1C1 = "=R9C": End If
   RngLig.Columns(1).FormulaR1C1 = "=""PF ""&ROW()-" & RngLig.Row - 1
   RngLig.Value = RngLig.Value
   Application.EnableEvents = True
   End Sub