0

I want to create a macro which copy some datas from another sheet and paste them (with a paste special value) to the final row of my range (from column I) of my active sheet (Sheet1). However, when I try to execute my macro, the last line of my macro is not read properly and it returns the error message "run time error 1004: application defined or object defined error" If someone can help me to fix this issue, that would be super. Many thanks. Xavier

Please find my VBA code below:

Sub insertfinalrow()
Worksheets("instructions macros").Activate
Range("N18:S18").Copy
Worksheets("Sheet1").Activate
Range("I" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues 
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
Xavi
  • 207
  • 1
  • 3
  • 20
  • 1
    I recommend to apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) first. Which would probably already solve your issue. – Pᴇʜ Nov 07 '18 at 10:49

1 Answers1

4

Try this and read up on how to avoid Select and Activate.

Worksheets("instructions macros").Range("N18:S18").Copy
Worksheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

You can avoid Copy and Paste altogether (when pasting values), which is slightly more efficient, though unlikely to be noticeable in your case.

With Worksheets(1).Range("N18:S18")
    Worksheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
SJR
  • 22,986
  • 6
  • 18
  • 26