Application defined or object defined error on pasting value in a range

Question

    Dim rOWIS As Integer

    Dim AOFF As Range   

    rOWIS = ActiveSheet.Range("A1000").End(xlUp).Row

    Range("A" & rOWIS).Copy

    AOFF = ActiveSheet.Range("A" & rOWIS).Offset(1, 0)

    ActiveSheet.Range(AOFF).Paste

On 6th line i am getting an error 1004.can you please help me on this.

range object doesn't suppor `Paste` method. Use PasteSpecial instead — Dmitry Pavliv, Apr 08 '14 at 13:29
Perhaps it didn't copy over in your code, but it looks like you're also missing `Set` from your range assignment... You would need to write `Set AOFF = ActiveSheet.Range("A" & rOWIS).Offset(1, 0)` — Dan Wagner, Apr 08 '14 at 14:36

score 0 · Accepted Answer · edited May 23 '17 at 12:05

1004 is a Object Defined Error which resulted from Range(AOFF).
Excel doesn't recognize such non-existent Range.
Dan pointed out a solution to propertly declare your variables using Set.
But what simoco pointed out is true as well, there is no .Paste method in Range Object.
so let's re-write your code to:

Dim rOWIS As Integer
Dim AOFF As Range   

With ActiveSheet
    rOWIS = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A" & rOWIS).Copy
    '~~> as what Dan commented
    Set AOFF = ActiveSheet.Range("A" & rOWIS).Offset(1, 0)
    '~~> as simoco commented
    AOFF.PasteSpecial xlPasteAll
End With

Above should work. I leave the testing to you.
Also refer to THIS to avoid runtimes.

Edit1: Here is a shorter version of your code applying what's discussed in the link.

Dim AOFF As Range

With ActiveSheet
    Set AOFF = .Range("A" & .Rows.Count).End(xlup)
    AOFF.Copy AOFF.Offset(1, 0)
End With

Application defined or object defined error on pasting value in a range

1 Answers1