XL 2016 Donnée à coté d'une cellule vide

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim n&, k&: n = Cells(Rows.Count, 1).End(3).Row
  k = n - 5: If k <= 0 Then Exit Sub
  With Worksheets("Feuil2")
    Cells(k, 2).Resize(6).Copy .[A1]: .Select
  End With
End Sub

=SIERREUR(DECALER(INDIRECT("Feuil1!$A"&EQUIV(9^9;Feuil1!A:A;1));LIGNES($1:1)-6;1);"")

Option Explicit

Dim m&, j&

Private Sub Job(i%)
  Dim n&, k&, h As Byte
  n = Cells(m, i).End(3).Row: k = n - 5: h = 6
  If k < 1 Then
    If IsEmpty(Cells(1, i)) Then Exit Sub
    k = 1: h = n
  End If
  With Worksheets("Feuil2")
    Cells(k, i).Resize(h).Copy .Cells(j, 2)
    j = j + h
  End With
End Sub

Sub Essai()
  If ActiveSheet.Name <> "Feuil1" Then Exit Sub
  Dim d%: d = Cells(1, Columns.Count).End(1).Column: If d = 1 Then Exit Sub
  Dim i%: m = Rows.Count: j = 1: Application.ScreenUpdating = 0
  For i = 2 To d: Job i: Next i: Worksheets("Feuil2").Select
End Sub