Range and .End(xlDown).Offset(1,0) Run-time error '1004'

Question

I'm trying to copy from Sheet 1 (A1:C1) and paste a special value with offset and xlDown in Sheet 2 and place the respective results in Range (B3:D3).

Sheet 1

Sheet 4 - (Required output)

I'm getting a Run-time error '1004' Application-defined or object-defined error.

Sub test()

Workbooks("testing.xlsm").Activate
Sheet1.Select
Range("A1:C1").Copy

Sheet2.Select
Range("B3").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Application.OnTime Now + TimeValue("00:00:10"), "test"
End Sub

If you're trying to find the last cell, `End(xlDown)` is unreliable. See [this approach](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Dec 16 '20 at 18:13

ZygD · Answer 1 · 2020-12-16T18:47:33.393

0

This should work:

Sub test()
Dim wsT As Worksheet
With Workbooks("testing.xlsm")
  Set wsT = .Sheets("Sheet2")
  wsT.Cells(wsT.Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(1, 3).Value = _
    .Sheets("Sheet1").Range("A1:C1").Value
  Application.OnTime Now + TimeValue("00:00:10"), "test"
End With
End Sub

It's more efficient version of your code.
However, if you prefer to keep your style, this is less efficient version:

Sub test()

Workbooks("testing.xlsm").Activate
Sheet1.Select
Range("A1:C1").Copy

Sheet2.Select
Cells(Sheet2.Rows.Count, "B").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Application.OnTime Now + TimeValue("00:00:10"), "test"
End Sub

edited Dec 16 '20 at 18:47

answered Dec 16 '20 at 18:22

ZygD

22,092
39
79
102

A side note: generally answers in the Excel/VBA tags [avoid `Select` and `Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Dec 16 '20 at 18:29
Please don't use `Select` and `Activate` in VBA, see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – FunThomas Dec 16 '20 at 18:29
@FunThomas - Thank you. I have updated the answer, pls remove your downvote ;) – ZygD Dec 16 '20 at 18:48
@BigBen - Thank you. I have updated the answer, pls remove your downvote ;) – ZygD Dec 16 '20 at 18:49
3

Not my downvote, but I think you should get rid of the second snippet entirely. Actually, if this is the answer, then the question is a duplicate of the canonical how to find the last row I linked to in the comment on the question. – BigBen Dec 16 '20 at 18:49

Range and .End(xlDown).Offset(1,0) Run-time error '1004'

1 Answers1