Split the Worksheet

[mrbalaje]

I have worksheet which contains 174501 lines of data in it. First line is the
heading, hence actual data is 174500 lines.

I want to split this huge worksheet in to multiple small worksheets of 1000
lines per file with the same heading in the master worksheet.

Thats means 174 worksheets which contains 1000 lines per file and 175th file
contains the remaining 500 lines. Is there any code available to acheive
this. Can you give an example of the code.

Thanks
Balaje

 

[Joel]

Sub splitpage()
PageCount = 1
Set OldSheet = ActiveSheet
With OldSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To LastRow Step 1000
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = .Name & " (" & PageCount & ")"
.Rows(1).Copy Destination:=ActiveSheet.Rows(1)
.Rows(RowCount & ":" & (RowCount + 999)).Copy _
Destination:=ActiveSheet.Rows(2)
PageCount = PageCount + 1
Next RowCount
End With
End Sub