ActiveSheet.PasteSpecial Paste:=xlPasteValues not working

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

Solution

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