How to Split Records to different files in Excel

“I have a large spreadsheet (22 MB, over 39,000 rows) which I need to split up into several different files. All of the data to be split is on one worksheet within this workbook. I would like to split the worksheet up (ex. "master.xls") into new files in multiples of 50 rows (ex. "chunk1-rows1-50.xls", chunk2-rows51-100.xls", etc.). I realize this is going to create a lot of smaller files, but I need to find a way to divide this huge sheet into several more manageable chunks. I have some knowledge with VB but not enough to get by with making a successful macro on my own. “

Do you have a similar problem like above? 

Here the solution 

1.  Use Macro- VB
2.  In your excel file, Press :’ALT’ and ‘F11’
3. In the Visul basic site, double click on the sheet that you want to split
4. Copy and paste this code to split the record into 500 records then execute the code .

Sub Macro1()

Dim rLastCell As Range

Dim rCells As Range

Dim strName As String

Dim lLoop As Long, lCopy As Long

Dim wbNew As Workbook

    With ThisWorkbook.Sheets(1)

    Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

   

        For lLoop = 1 To rLastCell.Row Step 500

        lCopy = lCopy + 1

            Set wbNew = Workbooks.Add

                .Range(.Cells(lLoop, 1), .Cells(lLoop + 500, .Columns.Count)).EntireRow.Copy _

                    Destination:=wbNew.Sheets(1).Range("A1")

            wbNew.Close SaveChanges:=True, Filename:="Chunk" & lCopy & "Rows" & lLoop & "-" & lLoop + 500

        Next lLoop

    End With

End Sub