How to separate one Excel file into multiple files.


I have an Excel file with 100,000 line items with two columns .
Does anyone know how I can separate that into 10 files with 10,000 line items each ?
Who is Participating?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try....

Sub SplitFile()
Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, i As Long
Dim FilePath As String, FileName As String
Application.ScreenUpdating = False

Set swb = ThisWorkbook          'Source Workbook
Set sws = swb.Sheets("Sheet1")  'Source Sheet
FilePath = swb.Path & "\"

lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr Step 10000
    Set wb = Workbooks.Add
    Set dws = wb.Sheets(1)
    FileName = i & " - " & i + 10000 - 1
    dws.Name = FileName
    sws.Range("A1:B1").Copy dws.Range("A1")
    sws.Range("A" & i).Resize(10000, 2).Copy dws.Range("A2")
    Application.DisplayAlerts = False
    wb.SaveAs FilePath & FileName, 51
    wb.Close True
Next i
Application.ScreenUpdating = True
MsgBox "Task completed!", vbInformation
End Sub

Open in new window

EirmanChief Operations ManagerCommented:
The simplest way would be to leave the original file intact and "Save As" 10 times with different names.
e.g. 0000-1000.xls   1001-2000.xls   2001-3000 ..... etc.

Then edit the 10 files one by one, deleting the relevant 9,000 lines in each file.
MilesLoganAuthor Commented:
Subodh thank you so much !! exactly what I was looking for ..
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.