Okay kids, this will probably be simple

Posted by Duane Kennerson on November 26, 2001 5:09 PM

I need a loop that will cut the contents from range b4 and paste it into b1, then cut the contents from b9,and paste it into b6, then cut b14 and paste to b11 etc...... for about 1500 rows. You may have noticed that all of the cells increment by 5 (the cut or paste cells increment by 5 ex. the difference between the paste cells b1, b6 and b11 is 5 if you know what I mean.) but I can't figure out how to make the loop. I can only get it to increment by 1. Please heeeeeeeeeeeeeeelp!

Posted by jacob on November 26, 2001 5:47 PM

This Should Work

Sub PasteIt()
x=4
y=1

a:
range("A" & x).select
selection.copy
range("A" & y).select
activesheet.paste
x=x+5
y=y+5
if x>1500 Then goto z:
goto a:
z:

End Sub

There are cleaner ways to do it but this will do the trick.

Posted by Bariloche on November 26, 2001 6:37 PM

The cleaner way ;-)

Duane,

Here's a cleaner way. :-)

Sub StepByFive()

Dim i As Double

Application.ScreenUpdating = False

For i = 4 To 2000 Step 5

Cells(i, 2).Cut
Cells(i - 3, 2).Select
Selection.Insert Shift:=xlDown
Next i

Application.ScreenUpdating = True

End Sub

enjoy

Posted by Metellus Cimber on November 27, 2001 5:43 AM

Here's a more cleaner way.

More cleaner way :-

Sub StepByFive()
Dim i%, c%
c = Range([B1], [B65536].End(xlUp)).Cells.Count
Application.ScreenUpdating = False
For i = 4 To c Step 5
Cells(i, 2).Cut
Cells(i - 3, 2).Insert
Next i
End Sub

Application.ScreenUpdating = False For i = 4 To 2000 Step 5 Cells(i, 2).Cut Cells(i - 3, 2).Select Selection.Insert Shift:=xlDown Next i Application.ScreenUpdating = True End Sub