Error message 'Runtime error '1004' Application-defined or object defined error when running copy/paste macro

Question

I'm trying to write a very basic macro which copies values from one sheet and pastes them into another. The macro works initially, then begins to return a Runtime error '1004' Application-defined or object defined error message. The code is:

Sub CopyPaste()
'
' CopyPaste Macro

    Sheets("Data Input").Range("C2:C11").Copy
    Sheets("Results").Range("A8").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
     Application.CutCopyMode = False
End Sub

possible duplicate of Run-time error: 1004 Range of object '_Worksheet' failed and stackoverflow.com/q/17980854/62576 — Ken White
– Ken White, Commented Dec 13, 2013 at 4:20
Hi thanks, I tried leaving out the '.End(xlDown).Offset(1, 0)' but that part of the code is supposed to ensure that the data is pasted into the next empty row rather than pasting straight over the top of old data, so getting rid of it isn't the ultimate solution. It works when I delete this, then works when I put it back into the code, then stops working again. Very frustrating! — user3097891
– user3097891, Commented Dec 13, 2013 at 5:40

Peter Albert · Accepted Answer · 2013-12-13 06:50:05Z

1

Instead of of starting at the top row and going down to the bottom, better do it the other way round - start at the bottom of the sheet and go up till you find the first data row. Else, you'll run into issue when you only have one or zero data rows (then the last sheet row will be returned) - or in case of gaps in the data you'll get the first gap.

Therefore, try this code instead:

Sub CopyPaste()
    Sheets("Data Input").Range("C2:C11").Copy
    Sheets("Results").Cells(Sheets("Results").Rows.count,1).End(xlUp) _
        .Offset(1).PasteSpecial Paste:=xlPasteValues Transpose:=True
     Application.CutCopyMode = False
End Sub

answered Dec 13, 2013 at 6:50

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error message 'Runtime error '1004' Application-defined or object defined error when running copy/paste macro

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related