Last Row Of Different Sheets & Duplicate Copy Paste

Question

I know the "Last Row" question has already come up several times but even when looking at existing threads I cannot find what is happening. It is the first time I write a Macro so I have only been able to get to a certain point I paste the code and ask the questions later:

Option Explicit

Sub Practice()

'Last Row Searcher

Dim Sht As Worksheet
Set Sht = ActiveSheet
Dim Last_Row As Long
With Sht
    Last_Row = .Range("A9999").End(xlUp).Row
End With

'Column A to D
Sheet9.Select
Range("A2:A" & Last_Row).Copy
Sheet11.Select
Range("D" & Last_Row).Select
ActiveSheet.Paste

'Column C to F
Sheet9.Select
Range("C2:C" & Last_Row).Copy
Sheet11.Select
Range("F" & Last_Row + 1).Select
ActiveSheet.Paste

'Column E to G
Sheet9.Select
Range("E2:E" & Last_Row).Copy
Sheet11.Select
Range("G" & Last_Row + 1).Select
ActiveSheet.Paste

'Column I to L
Sheet9.Select
Range("I2:I" & Last_Row).Copy
Sheet11.Select
Range("L" & Last_Row + 1).Select
ActiveSheet.Paste

End Sub

Question 1:

When I paste what I have copied to the other worksheet it directly pastes things in the "Last_Row" from the previous worksheet instead of looking for the new "Last_Row" of the Active Sheet. Is there a way around this?

Question 2

I repeat the same code several times but with different columns, because they are not together I copy column A to D, then C to F, etc.

It is working for me, but out of curiosity, is there a way to do it all at once?

Answer 1

Adding another answer here because my previous answer was incomplete (and it's been bothering me since yesterday!). Since this is a repetitive bit of code, I would separate the column-copy into it's own sub. Your logic becomes very simple in your main routine.

Option Explicit

Sub test()
    CopyMyColumn Sheet1.Range("A1").EntireColumn, Sheet1.Range("D1").EntireColumn
    CopyMyColumn Sheet1.Range("C1").EntireColumn, Sheet1.Range("F1").EntireColumn
    CopyMyColumn Sheet1.Range("E1").EntireColumn, Sheet1.Range("G1").EntireColumn
    CopyMyColumn Sheet1.Range("I1").EntireColumn, Sheet1.Range("L1").EntireColumn
End Sub

Private Sub CopyMyColumn(ByRef srcColumn As Range, ByRef dstColumn As Range)
    '--- copies the source column from row 2 to the end of the data, to
    '    the destination column, appending to the end of the existing data
    Dim srcLastRow As Long
    With srcColumn
        srcLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    Dim dstLastRow As Long
    With dstColumn
        dstLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

    Dim src As Range
    Dim dst As Range
    Set src = srcColumn.Cells(2, 1).Resize(srcLastRow, 1)
    Set dst = dstColumn.Cells(1, 1).Offset(dstLastRow, 0).Resize(srcLastRow, 1)
    dst.Value = src.Value
End Sub

Answer 2

(First Empty Row After) Last Non-Empty Row

Option Explicit

Sub Practice()

'Last Row Searcher

    Const frSrc As Long = 2                 ' Source First Row
    Const strSrc As String = "A,C, E, I"    ' Source Column Letters
    Const strTgt As String = "D, F,G, L"    ' Target Column Letters

    Dim wsSrc As Worksheet  ' Source Worksheet
    Dim wsTgt As Worksheet  ' Target Worksheet
    Dim rngSrc As Range     ' Source Column Range
    Dim rngTgt As Range     ' Target Column Range
    Dim vntS As Variant     ' Source Column Array
    Dim vntT As Variant     ' Target Column Array
    Dim lrSrc As Long       ' Source Last Non-Empty Row
    Dim frTgt As Long       ' Target First Row After Last Non-Empty Row
    Dim i As Long           ' Source and Target Array Elements Counter
    Dim colSrc As String    ' Source Column Letter
    Dim colTgt As String    ' Target Column Letter

    ' Beware, you are using CodeNames, which are not the names on the TAB.
    Set wsSrc = Sheet9
    Set wsTgt = Sheet11

    ' Populate Column Arrays (vntS, vntT).
    vntS = Split(strSrc, ","): vntT = Split(strTgt, ",")

    ' Loop through elements of Source (or Target) Column Array.
    For i = 0 To UBound(vntS)
        ' Calculate Column Letter (colSrc, colTgt)
        colSrc = Trim(vntS(i)): colTgt = Trim(vntT(i))
        ' Calculate Source Last Non-Empty Row.
        lrSrc = wsSrc.Range(colSrc & wsSrc.Rows.Count).End(xlUp).Row
        ' Calculate Target First Row After Last Non-Empty Row.
        frTgt = wsTgt.Range(colTgt & wsTgt.Rows.Count).End(xlUp).Row + 1
        ' Calculate Source Column Range.
        Set rngSrc = wsSrc.Range(colSrc & frSrc & ":" & colSrc & lrSrc)
        ' Calculate Target Column Range.
        Set rngTgt = wsTgt.Range(colTgt & frTgt).Resize(rngSrc.Rows.Count)
        ' Write values of Source Column Range to Target Column Range.
        rngTgt.Value = rngSrc.Value
    Next

End Sub

EDIT:

Sub Practice2()

'Last Row Searcher

    Const frSrc As Long = 2                 ' Source First Row
    Const strSrc As String = "A,C, E, I"    ' Source Column Letters
    Const strTgT As String = "D, F,G, L"    ' Target Column Letters

    Dim wsSrc As Worksheet  ' Source Worksheet
    Dim wsTgt As Worksheet  ' Target Worksheet
    Dim rngSrc As Range     ' Source Column Range
    Dim rngTgt As Range     ' Target Column Range
    Dim vntS As Variant     ' Source Column Array
    Dim vntT As Variant     ' Target Column Array
    Dim lrSrc As Long       ' Source Last Non-Empty Row
    Dim frTgt As Long       ' Target First Row After Last Non-Empty Row
    Dim i As Long           ' Source and Target Array Elements Counter
    Dim colSrc As String    ' Source Column Letter
    Dim colTgt As String    ' Target Column Letter

    ' Beware, you are using CodeNames, which are not the names on the TAB.
    Set wsSrc = Sheet9
    Set wsTgt = Sheet11

    ' Populate Column Arrays (vntS, vntT).
    vntS = Split(strSrc, ",")
    vntT = Split(strTgT, ",")

    ' Calculate Target First Row After Last Non-Empty Row.
    frTgt = wsTgt.Range(Trim(vntT(0)) & wsTgt.Rows.Count).End(xlUp).Row + 1

    ' Loop through elements of Source (or Target) Column Array.
    For i = 0 To UBound(vntS)
        ' Calculate Column Letter (colSrc, colTgt)
        colSrc = Trim(vntS(i)): colTgt = Trim(vntT(i))
        ' Calculate Source Last Non-Empty Row.
        lrSrc = wsSrc.Range(colSrc & wsSrc.Rows.Count).End(xlUp).Row
        ' Calculate Source Column Range.
        Set rngSrc = wsSrc.Range(colSrc & frSrc & ":" & colSrc & lrSrc)
        ' Calculate Target Column Range.
        Set rngTgt = wsTgt.Range(colTgt & frTgt).Resize(rngSrc.Rows.Count)
        ' Write values of Source Column Range to Target Column Range.
        rngTgt.Value = rngSrc.Value
    Next

End Sub

Answer 3

You need to set define the "last row" more clearly. In your case, I believe what you want is to find the last row of the source data AND then paste it after the last row of your destination sheet. So try something like this:

Dim srcWS As Worksheet
Set srcWS = Sheet9

Dim dstWS As Worksheet
Set dstWS = Sheet11

Dim srcLastRow As Long
With srcWS
    srcLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim dstLastRow As Long
With dstWS
    dstLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

srcWS.Range("A2:A" & srcLastRow).Copy
dstWS.Range("D" & dstLastRow).Paste

No Select or ActiveSheet is necessary (which you should avoid whenever you can).