XL 2010 Copier coller dernière ligne remplie d'un tableau

=INDEX(A7:A54;NB.SI(A7:A54;"<>"))

=INDEX(B$9:B$55;EQUIV(MAX($E$9:$E$55);$E$9:$E$55;0))

=SIERREUR(SI(ESTNUM(INDEX(B:B;derlig));INDEX(B:B;derlig);""&INDEX(B:B;derlig));"")

=GRANDE.VALEUR(SI(Tableau2<>"";LIGNE(Tableau2));1)

=GRANDE.VALEUR(SI(ESTNUM(LN(Tableau2<>""));LIGNE(Tableau2));1)

Sub CopierFeuilleMultiple()
Dim n As Integer, i As Integer
On Error Resume Next
Application.ScreenUpdating = False
    n = InputBox("Combien de copies voulez-vous créer?")
    If n > 0 Then
        For i = 1 To n
            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
            ActiveSheet.Name = i
            ActiveSheet.[C5] = i
        Next
    End If
End Sub

Sub CopierFeuilleMultiple()
Dim n As Integer, i As Integer
With [Tableau2].Parent
    .Name = 1
    .Activate
    On Error Resume Next
    n = InputBox("Combien de copies voulez-vous créer?")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = 2 To n: Sheets(CStr(i)).Delete: Next i
    For i = 2 To n
        .Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = i
        ActiveSheet.Move After:=Sheets(CStr(i - 1)) 'tri
        ActiveSheet.Range("C5") = i
    Next i
    .Activate
End With
End Sub

Private Sub Worksheet_Activate()
Dim zonecopie$, ncol%, ligdeb&, coldeb%, lig&, w As Worksheet, i&
zonecopie = "B7:J7"
ncol = Range(zonecopie).Columns.Count
ligdeb = 8
coldeb = 2
lig = ligdeb
Application.ScreenUpdating = False
Rows(ligdeb & ":" & Rows.Count).Delete 'RAZ
For Each w In Worksheets
    i = Val(w.Name)
    If i Then
        Cells(lig, coldeb).Resize(, ncol) = w.Range(zonecopie).Value 'copie les valeurs
        lig = lig + 1
    End If
Next
If lig = ligdeb Then Exit Sub
With Cells(ligdeb, coldeb).Resize(lig - ligdeb, ncol)
    .Sort .Columns(1), xlAscending, Header:=xlNo 'tri
    .Interior.Color = RGB(164, 204, 228) 'bleu
    .Borders.Weight = xlThin 'bordures
End With
End Sub