CODEWITHSUNDEEP
vbaexcel
Psyshadow
Psyshadow

Reputation: 55

Cut and Paste Loop with VBA in Excel Error 1004

For my current project, I'm trying to move a group of cells down in a worksheet up to a certain number of rows. i figured the easiest way to do this is to cut the 2nd to last group and paste it in place of the last group, then cut the 3rd to last group and paste it in the place where the 2nd to last was and so on (since I don't want to be the list to be ever expanding) using the following code:

For i = 298 To 8 Step -5
Sheets("Calc").Range(Cells(i, 130), Cells(i + 4, 130)).Cut
Sheets("Calc").Range(Cells(i + 5, 1)).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next i

However, I always get a runtime error 1004 on the second line of code.

Can anyone help me out?

Upvotes: 1

Views: 1094

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use this one instead:

With Sheets("Calc")
   For i = 298 To 8 Step -5
       .Range(.Cells(i, 130), .Cells(i + 4, 130)).Cut Destination:=.Cells(i + 5, 130)
   Next i
End With

Howerver, as I see, you can do it without loop:

With Sheets("Calc")
    .Range(.Cells(8, 130), .Cells(302, 130)).Cut Destination:=.Cells(13, 130)
End With

Btw, I've changed your destination range from Sheets("Calc").Cells(i + 5, 1) to Sheets("Calc").Cells(i + 5, 130) (as I understood your question, you want to move cells down, but not across)

Upvotes: 2

D_Bester
D_Bester

Reputation: 5911

You cannot use PasteSpecial with Cut

You can either copy and paste as values

OR

You can cut and paste

Upvotes: 1

Related Questions