0

It is showing error 1004 on ** --- ** line in this code. PLease help

Sub test()

Workbooks("OI ANALYSIS.xlsm").Activate
Sheet3.Select
Range("A16:C16").Copy

Sheet4.Select

**Range("B2").End(xlDown).Offset(1, 0).Select**


Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Application.OnTime Now + TimeValue("00:00:01"), "test"
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Monil Mathur
  • 1
  • 1
  • 1
    If you're trying to find the last row, see [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for a better approach. – BigBen Dec 07 '20 at 13:58
  • 1
    you most likely are finding the last row with `Range("B2").End(xlDown)` then with `.Offset(1, 0)` you are trying to select the row below the last row and there is no row to select. As @BigBen stated you should follow the example in the link. – Scott Craner Dec 07 '20 at 14:28
  • Due to [VBA Error codes](https://onlinelibrary.wiley.com/doi/pdf/10.1002/9781118257616.app3): "**1004 Application-defined or object-defined error.** - This is a very common catch-all error message. This error occurs when an error doesn’t correspond to an error defined by VBA. In other words, the error is defined by Excel (or some other object) and is propagated back to VBA." @MonilMathur – T.M. Dec 07 '20 at 18:38

1 Answers1

-1

Try this-

Sub test()

Workbooks("OI ANALYSIS.xlsm").Activate
Worksheets("Sheet3").Select
Range("A16:C16").Copy

Worksheets("Sheet4").Select

Range("B2").End(xlDown).Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

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

The Sheet3.Select syntax isn't correct in VBA. Run-time error 1004 is common when you don't specify the range correctly or when an invalid name is used.

ameyashete
  • 27
  • 6
  • 3
    `The Sheet3.Select syntax isn't correct in VBA.` That's actually not true. Though generally a VBA answer should [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) anyway so you may want to consider revising the answer. – BigBen Dec 07 '20 at 14:19