Optimizing code efficiency

[RafFiniert]

Among other tips/tricks, I read that I should rid my code of all .Select statements. I have done so by and large. However, I am stuck on this one.

(Original code that works)

Worksheets("DM2").Select
Worksheets("DM2").Range("A5:J5").Select
Worksheets("DM2").Range("A5:J5").Copy
Worksheets("Routes").Select
Worksheets("Routes").Range("A3:A3").Select
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("FromHereSubsequently").PasteSpecial xlPasteValues

Worksheets("NamedRanges").Range("DriveTime").Copy
Worksheets("Routes").Range("H3:H3").Select
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("ArriveTime").Copy
Worksheets("Routes").Range("I3:I3").Select
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("EndTime").Copy
Worksheets("Routes").Range("J3:J3").Select
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).PasteSpecial xlPasteValues

(What I tried that doesn't work)

Worksheets("Routes").Range("A3:A3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Worksheets("DM2").Range("A5:J5").Value
Worksheets("NamedRanges").Range("FromHereSubsequently").Value = Worksheets("DM2").Range("A5:J5").Value

Worksheets("Routes").Range("H3:H3").Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).Value = Worksheets("NamedRanges").Range("DriveTime").Value
Worksheets("Routes").Range("I3:I3").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).Value = Worksheets("NamedRanges").Range("ArriveTime").Value
Worksheets("Routes").Range("J3:J3").Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).Value = Worksheets("NamedRanges").Range("EndTime").Value


Based on how I got rid of my other .Select/.Copy statements, it seems intuitive that this should work. Only it doesn't.

 

[footoo]

With your revised code, the source range and destination range need to be the same size.

 

[kvsrinivasamurthy]

Try

Worksheets("DM2").Range("A5:J5").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("FromHereSubsequently").PasteSpecial xlPasteValues

Worksheets("NamedRanges").Range("DriveTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("ArriveTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("EndTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).PasteSpecial xlPasteValues
Application.CutCopyMode = False


'(What I tried that doesn't work)

 

[RafFiniert]

With your revised code, the source range and destination range need to be the same size.

Thanks for the response. Unfortunately, they are not the same size. The source (1 row x 5 cols) is being copied to the bottom of an existing list.

 

[RafFiniert]

Try

Worksheets("DM2").Range("A5:J5").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("FromHereSubsequently").PasteSpecial xlPasteValues

Worksheets("NamedRanges").Range("DriveTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("ArriveTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).PasteSpecial xlPasteValues
Worksheets("NamedRanges").Range("EndTime").Copy
Worksheets("Routes").Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).PasteSpecial xlPasteValues
Application.CutCopyMode = False


'(What I tried that doesn't work)

This worked--thanks.

 

[kvsrinivasamurthy]

welcome.

 

[footoo]

Thanks for the response. Unfortunately, they are not the same size. The source (1 row x 5 cols) is being copied to the bottom of an existing list.

If the source is a fixed size, then you can fix the destination size with the Resize property.