Dynamic range of data to paste into another sheet

Question

I would like to copy all data in Sheet 1 Column A to Sheet 2

Problem is I want the paste the data into sheet 2 but start from Cell A13

Example Sheet 1 A2 Value to be pasted into Sheet 2 Cell A13

This is the code I have which is pasting the value of Column A sheet 1 to Column A sheet but but I cannot change the start cell

Sub test1()
    ' test1 Macro

    Application.ScreenUpdating = False

    Dim s1 As Excel.Worksheet
    Dim s2 As Excel.Worksheet
    Dim iLastCellS2 As Excel.Range
    Dim iLastRowS1 As Long

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")

    ' get last row number of A in Sheet1
    iLastRowS1 = s1.Cells(s1.Rows.Count, "A").End(xlUp).Row

    ' get last AVAILABLE cell to past into
    Set iLastCellS2 = s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0)

    'copy&paste into sheet2
    s1.Range("A2", s1.Cells(iLastRowS1, "A")).Copy iLastCellS2

    Application.ScreenUpdating = True
End Sub

Thank you

Answer 1

Try this:

Sub CustomCopy()
    Dim lastRow As Long
    With Sheets("Sheet1")
        'first we find last row in column A (in sheet1)
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        'now we copy until found row number and paste it to Sheet2 starting from cell A13
        .Range("A1:A" & lastRow).Copy Sheets("Sheet2").Range("A13:A" & (lastRow + 12))
    End With
End Sub

Answer 2

If you want to always paste direct to A13 (not dynamic) then:

  s1.Range("A2", s1.Cells(iLastRowS1, "A")).Copy s2.Cells(13,"A")

If you always want it to start at at least A13:

Set iLastCellS2 = IIf(s2.Cells(s2.Rows.Count, "A").End(xlUp).Row < 13, s2.Cells(13, "A"), s2.Cells(s2.Rows.Count, "A").End(xlUp).Offset(1, 0))
s1.Range("A2", s1.Cells(iLastRowS1, "A")).Copy iLastCellS2