VBA copy range to last row & paste on new sheet, first empty row after cell A19

Question

I am trying to copy range A6:L, down to the last row from Sheet "Step 1". Then paste onto Sheet "Vendor_Copy", but pasting on the first empty row after A19.

Here is what I have, which does everything but paste in the correct place. It's pasting starting in A1, which is a blank cell. However, even if I fill all rows from 1 - 19 with the number 1, it still pastes in A1 on sheet "Vendor_Copy".

Thank you for your help!

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim lr As Long
    
Set sht1 = Worksheets("Step 1")
Set sht2 = Worksheets("Vendor_Copy")

lr = sht2.Range("A19").End(xlDown).Row

sht1.Activate
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
    
sht2.Activate
Cells(lastrow + 1, 1).PasteSpecial

sht2.Cells(Rows.Count, "A").End(xlUp).Offset(1,0).PasteSpecial — Tim Williams
Avoid the use of xlDown to find the last row. You may want to see THIS Similarly avoid the use of xlToRight to find last column. — Siddharth Rout

Variatus Variatus · Accepted Answer · 2021-02-24T06:47:18

Don't use the Selection object. It's meant as an interface between screen and VBA. Instead, use the Range object which lets you address the worksheet directly.

The row on Sheet2 that you wish to address is the next free one but not smaller than 19. In Excel terms that is the result of the MAX() function, incorporated in the code below.

Dim Sht1    As Worksheet
Dim Sht2    As Worksheet
Dim Rng     As Range
Dim Cl      As Long                     ' Column: last used
Dim Rt      As Long                     ' Row: target
    
Set Sht1 = Worksheets("Step 1")
Set Sht2 = Worksheets("Vendor_Copy")

With Sht1
    Cl = .Cells(6, .Columns.Count).End(xlToLeft).Column
    Set Rng = .Range(.Cells(6, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Resize(, Cl)
End With

With Sht2
    ' Columns(1) = Columns("A")
    Rt = WorksheetFunction.Max(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 19)
    Rng.Copy Destination:=.Cells(Rt, 1)
End With

Note that Cells(6, 1).End(xlDown).Row - or Range("A6").End(xlDown).Row will give you the address of the last used cell below A6 that is followed by a blank. Cells(Rows.Count, 1).End(xlUp).Row will give you the first used cell in column A looking from the sheet's bottom (Rows.Count). The difference is that looking from top down the next blank might not be the last used cell in the column. It's the same difference between Cells(6, 1).End(xlToRight).Column and Cells(6, Columns.Count).End(xlToLeft).Column. Looking from left to right you find the last used column. Looking from right to left you find the first blank.

VBA copy range to last row & paste on new sheet, first empty row after cell A19

2 Answers