VBA Select & Copy ignores first line
I have some code that I could use. This script I wrote is designed to copy the range from the first line to the last line to another sheet. It works fine if there are more than two lines containing data, but if there is only one line (the first line), it ignores that data, doesn't copy it, and throws an error.
Is there anything you can identify that can fix this code? I've searched endlessly to no avail. Thank!
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
+3
source to share
2 answers
End(xlDown)
will have unwanted effects when using only one line. It is much safer to use the xlUp
bottom of the worksheet instead of
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
+4
source to share
Replace,
ws3.Range("T1:U1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
... from,
with ws3
.Range(.cells(1, "T"), .cells(.rows.count, "U").end(xlup)).copy
end with
When looking for the last nonblank cell, look from the bottom up; not from top to bottom.
+2
source to share