Private Sub Worksheet_Activate()
Dim pas, source, resu(), i&, n&
pas = 4
source = Sheets("Feuil1").[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To UBound(source) * pas, 1 To 1)
For i = 1 To UBound(source)
resu(1 + (i - 1) * pas, 1) = source(i, 1)
Next
n = UBound(resu)
With [A1] '1ère cellule de destination, à adapter
.Resize(n) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub