excel 2010 returning: Run-time error '1004': "PasteSpecial method of Range class failed

Question

This VBA code has started returning the error message: "Run-time error '1004': "PasteSpecial method of Range class failed"

I use the VBA code to copy and paste a few cells to the next row. I often run it several times in succession. It never fails on the first pass. It worked for years before beginning to fail.

Sub CopyInfoToLineBelowSamePO_Ctrl_Shft_Y()
'
' CopyInfoToLineBelowSamePO_Ctrl_Shft_Y Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-1, 5).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -5).Range("A1").Select
End Sub

I think the failure occurs at this point (first appearance):

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

That error message basically means that there's nothing in the clipboard, but I have no idea why that happens. I tried your code and it worked fine for me. — Bruno 82

elmer007 elmer007 · Accepted Answer · 2017-01-13T22:57:32

I'm not sure why your code would start failing when used in the same situations it was used previously. However, I would suggest using the code below to perform the same operation:

Sub CopyInfoToLineBelowSamePO_Ctrl_Shft_Y()

ActiveCell.Offset(1, 0).Value2 = ActiveCell.Value2
ActiveCell.Offset(1, 5).Value2 = ActiveCell.Offset(0, 5).Value2
ActiveCell.Offset(1, 0).Select

End Sub

Instead of using the clipboard to copy and paste the values around, this just directly sets the values of the cells in the next row to the values above them, then moves the selection to the next cell down.

This will avoid any copy/paste errors, and it is trivial to troubleshoot this code should it ever fail.

excel 2010 returning: Run-time error '1004': "PasteSpecial method of Range class failed

1 Answers