CODEWITHSUNDEEP
vbaexcel
Wxby
Wxby

Reputation: 51

VBA Select & Copy ignores first row

I have a code I could use some help on. This script I wrote is meant to copy a range from the first row to the last row to another sheet. It works perfectly if there are more than 2 rows containing data, but if there is only 1 row (the first row), it ignores that data, does not copy it, and outputs an error.

Is there anything you can identify that might fix this code? I've searched endlessly with no results. Thanks!

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("HCA")
Set ws2 = Worksheets("FormA")
Set ws3 = Worksheets("NamedRange")

ws3.Range("T1:U1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FormA").Select
ws2.Range("AQ7").PasteSpecial

Upvotes: 3

Views: 1093

Answers (2)

CallumDA
CallumDA

Reputation: 12113

End(xlDown) will have undesirable effects when there is only one row being used. It's much more reliable to use xlUp from the bottom of your worksheet instead

Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet

    Set ws1 = Worksheets("HCA")
    Set ws2 = Worksheets("FormA")
    Set ws3 = Worksheets("NamedRange")

    With ws3
        .Range(.Range("T1"), .Cells(.Rows.Count, "U").End(xlUp)).Copy
    End With
    ws2.Range("AQ7").PasteSpecial
End Sub

Upvotes: 4

user4039065
user4039065

Reputation:

Replace,

ws3.Range("T1:U1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

... with,

with ws3
    .Range(.cells(1, "T"), .cells(.rows.count, "U").end(xlup)).copy
end with

When looking for the last non-blank cell, look from the bottom up; not from the top down.

Upvotes: 3

Related Questions