1

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
pnuts
  • 58,317
  • 11
  • 87
  • 139
user3097891
  • 11
  • 2
  • possible duplicate of [Run-time error: 1004 Range of object '\_Worksheet' failed](http://stackoverflow.com/questions/16927378/run-time-error-1004-range-of-object-worksheet-failed) and http://stackoverflow.com/q/17980854/62576 – Ken White Dec 13 '13 at 04: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 Dec 13 '13 at 05:40

1 Answers1

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
Peter Albert
  • 16,917
  • 5
  • 64
  • 88