Looping a selection of rows

A

Andre Kruger

Hi

I need to create a loop which will select 4 rows copy and paste it to a new
sheet. then go to the Next 4 rows and copy them to a new sheet. I need this
to happen untill the row is blank (empty).

Any idea how to do this loop?
 
D

Dave Peterson

I used column A to find that last used row.

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long

Set CurWks = Worksheets("sheet1")

myStep = 4

With CurWks

FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step myStep
Set NewWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))
.Rows(iRow).Resize(myStep).Copy _
Destination:=NewWks.Range("a1")
Next iRow

End With

End Sub
 
Top