cut and paste...but automatic!!!

[Omar Raffi]

hello,

i have a number of rows (around 2k) with data in columns.
due to work needed, i need that each sheet contains no more than 50 rows.

solutions:
- i i'll manually cut and copy all rows till they will be no more that 50
per sheet
- i find an automatic way to perform the same

anybdy can help me in this??

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long

Set wks = Worksheets("Sheet1")

myStep = 50

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

For iRow = FirstRow To LastRow Step myStep
Set newWks = Worksheets.Add
.Rows(iRow).Resize(myStep).Copy _
Destination:=newWks.Range("A1")
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm