Split Excel file into multiple files based on number of rows

Hi,

I have an excel file containing 9000-10000 rows. I want to split this into multiple files of 1000 rows each.

There's a small condition to this. Say that the number of records is around 9400 (not exactly 9000 or 10000).Then I want 9 files containing 1000 each and a 10th file with these 400 rows. Basically, if the records increase it should push it to the last file, untill it reaches a 1000 and then create a new file.


Any help is much appreciated. Thanks a lot!

- KC

Try this on a copy of your workbook

Sub Maybe_This()
Dim j As Long
Application.ScreenUpdating = False
    For j = 1 To Sheets("sheet1").Cells.SpecialCells(11).Row Step 1000
        Sheets.Add , Sheets(Sheets.Count)
        Sheets("sheet1").Rows(j).Resize(1000).Copy Sheets(Sheets.Count).Cells(1)
    Next
    Sheets("sheet1").Cells.ClearContents    '<---- If you want Sheet1 cleared of contents
    Application.ScreenUpdating = True
End Sub

Thanks jolivanes. It works I tested it.

Is there a way to schedule this to run as and when new records are added or deleted?