CODEWITHSUNDEEP
excelvba
user3097891
user3097891

Reputation: 11

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

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

Upvotes: 1

Views: 1243

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

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

Upvotes: 1

Related Questions