Excel VBA Run-time error '1004' Paste method of Worksheet class failed

[PatrickW]

Disclaimer: I know next to nothing about VBA, made this using "Record Macro" in Excel.

The purpose of this is to break a continuous vertical list of data into horizontal rows for each entry. Whenever I've encountered the run-time 1004 error in the past, simply closing and re-opening the file would fix the problem, however this is not doing the trick anymore. It debugs at the first ActiveSheet.Paste line. Thanks for your help in advance.


Sub MoveIt()
'
' MOVEIT Macro
'
' Keyboard Shortcut: Ctrl+y
'
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-2, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, -2).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-3, 3).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(4, -3).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-4, 4).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(5, -4).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-5, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(6, -5).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-6, 6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(7, -6).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-7, 7).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(8, -7).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-8, 8).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(9, -8).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-9, 9).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(10, -9).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-10, 10).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(11, -10).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-11, 11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(12, -11).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-12, 12).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(13, -12).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-13, 13).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(14, -13).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-14, 14).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(15, -14).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-15, 15).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(16, -15).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-16, 16).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(18, -16).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Cut
ActiveCell.Offset(-17, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
End Sub

[Koman]

I found this reference link of Microsoft which has some solution for your issue. Try to click on below link and read the article. Another thing that I noticed is the issue does not appears when the data is not filtered. So you can try to run the macro first on unfiltered data and then check back.

You receive a "Runtime error 1004" error message when you try to copy and to paste filtered data programmatically in an Excel 2003 workbook