Shared ramblings/ findings

Thursday, April 21, 2011

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

No comments: