Copy and Paste Runtime Error - Newbie, really struggling here!

**lgjmac** · 05-06-2013, 05:06 PM

Made this code...

Trying to work out why I get "Method PasteSpecial of Object Range Failed" Runtime error 1004

It gets as far as the pastespecial line.

What I want the macro to do is very simple:
-select data from the active cell down to the last cell in the column
-cut that data
-move 3 rows down, same column
-paste the data
-move 1 row down
-repeat until the active cell is empty (it gets to the bottom of the data)

PLEASE PLEASE PLEASE HELP

Sub Macro2()
'
' Macro2 Macro
'Dim Start
'Dim i As Integer

ActiveCell.Name = "start"
'Cells.Range(Start, ActiveCell.End(xlDown)).Count = "i"
'Cells.End(xlDown).Select
'ActiveCell.Name = "end"
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
With Selection.Cut
End With
ActiveCell.Offset(3, 0).Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

End Sub

**TMS** · 05-06-2013, 05:30 PM

You can't Cut and Paste Special. If you Cut cells manually, you will find the option to Paste Special is not available.

You'd need to Copy, Paste Special and then go back to delete the cells you don't want.

Although you have explained what you are trying to do, I don't really understand what you are trying to achieve. Consequently, I can't really advise you on the best approach.

You may get more input if you post a sample workbook and explain what you are starting with and what you want to end up with.

Regards, TMS

**Alf** · 05-06-2013, 05:43 PM

You are selecting a rang from a cell named "start" down to the last cell in that column i.e. row 65536. Then you cut this range and try to paste it 3 rows futher down. So the end of this range should land at row 65539 but as I said before the Excel worksheet (version 2003) only contains 65536 rows.

This is the reason you get the error message.

Alf