XL 2010 Copier / Coller d'une feuille à l'autre en VBA...

Sub Essai()
    Sheets("Coll N").Range(Cells(2, 1), Cells(10, 19)).Copy _
    Destination:=Sheets("Anc Coll").Cells(1, 1).End(xlUp).Offset(0, 0)
End Sub

Option Explicit

Sub Test_Ivan_1()
Dim WSsource As Worksheet, WScible As Worksheet
Dim RngSource As Range
Dim b As Integer, i As Integer

Set WSsource = ThisWorkbook.Worksheets("Coll N")
Set WScible = ThisWorkbook.Worksheets("Anc Coll")

For i = 2 To 10
        Set RngSource = WSsource.Range(WSsource.Cells(i, 1), WSsource.Cells(i, 19))
        b = b + 1
        RngSource.Copy WScible.Cells(b, 1)
Next i

Application.CutCopyMode = False

End Sub

'=============================================================

Sub Test_Ivan_2()
Dim WSsource As Worksheet, WScible As Worksheet
Dim RngSource As Range

Set WSsource = ThisWorkbook.Worksheets("Coll N")
Set WScible = ThisWorkbook.Worksheets("Anc Coll")

Set RngSource = WSsource.Range("S2:S" & WSsource.Range("S1000").End(xlUp).Row) 'Ajuste dynamiquement le nombre de lignes

WScible.Range("A1").Resize(RngSource.Rows.Count, 1).Value = RngSource.Value

End Sub