VBA Code to always copy the last row with selected cells only

Question

I get stuck in selecting to always copy the last row with selected cells only (not the whole row). I tried to record a macro, but then it won't copy the last row with selected cells because the code is locating to particular cells.

I have column A-Z in sheet A, and I would like to copy column A:E, R:T, Y:Z, and only copy the last row for each time. Here is the code I have tried. Which part I can change?

Worksheets("A").Select
Range("A2").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("A16:E16,R16:T16,Y16:Z16").Select
Range("Y16").Activate
Selection.Copy
Worksheets("B").Activate
R = Worksheets("B").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("B").Range("A" & R + 1).Select
ActiveSheet.PasteSpecial

You must read [How to avoid using Select in Excel VBA?](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Harun24hr, Nov 26 '20 at 02:18

score 1 · Answer 1 · answered Nov 26 '20 at 02:17

1

Try below sub-

Sub CopySpecificRange()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRowA As Long, lRowB As Long
Dim rngAE As String, rngRT As String, rngYZ As String

    Set ws1 = Worksheets("A")
    Set ws2 = Worksheets("B")
    
        lRowA = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
        lRowB = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
        
        rngAE = "A" & lRowA & ":E" & lRowA
        rngRT = "R" & lRowA & ":T" & lRowA
        rngYZ = "Y" & lRowA & ":Z" & lRowA
        
        ws1.Range(rngAE).Copy ws2.Cells(lRowB + 1, "A")
        ws1.Range(rngRT).Copy ws2.Cells(lRowB + 1, "R")
        ws1.Range(rngYZ).Copy ws2.Cells(lRowB + 1, "Y")
    
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

answered Nov 26 '20 at 02:17

Harun24hr

30,391
4
21
36

Harun, Thanks for the suggestion. However, the above code will copy the whole row. – kipper Nov 26 '20 at 06:24
@kipper No. It will copy specific cells from last row. – Harun24hr Nov 26 '20 at 06:29
Yes, you are right. That's my mistake, I accidentally didn't set ws1 and ws2 to nothing – kipper Nov 26 '20 at 06:53

Variatus · Accepted Answer · 2020-11-26T03:09:51.157

You don't need to Select anything. Just tell Excel what you are talking about. Instead of saying, "about the worksheet I just selected", say "about Worksheet("A")".

Sub TransferDataFromLastRow()

    Dim Rs      As Long             ' source row
    
    With Worksheets("A")
        Rs = .Cells(.Rows.Count, "A").End(xlUp).Row
        Application.Union(.Range(.Cells(Rs, "A"), .Cells(Rs, "E")), _
                          .Range(.Cells(Rs, "R"), .Cells(Rs, "T")), _
                          .Range(.Cells(Rs, "Y"), .Cells(Rs, "Z"))).Copy
    End With
    
    With Worksheets("B")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Here's the narrative:-

"Talking about Worksheets("A"), take note of the last used row in the variable Rs. Then merge the 3 specified ranges into a Union and copy its value to the clipboard."
"Talking about Worksheets("B"), paste the contents of the clipboard to the range starting at the cell below the last used cell in column A."
"Clear the clipboard."

Note that a cell is defined by row and column - syntax Cells([Row], [Column]) - where the column can be a number or the column's alphabetic ID. A range is defined by its first and last cells, like Set MyRange = Range(Cells, 1, 1), Cells(15, 26)) = Set MyRange = Range(Cells, 1, "A"), Cells(15, "Z"))

Hi Variatus, the code that you suggested works really well. Thank you for the explanation. — kipper, Nov 26 '20 at 06:26

VBA Code to always copy the last row with selected cells only

2 Answers2