How to split Excel file into multiple files?

Best solution approach is VBA Code for YOUR Situation (Fixed for Mac):

Sub SplitByRows()
    Dim ws As Worksheet, NewWb As Workbook
    Dim LastRow As Long, SplitSize As Long
    Dim i As Long, FileCount As Long
    Dim FilePath As String
    
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    SplitSize = 5000                    ' ← Change as needed
    FilePath = ThisWorkbook.Path
    
    Application.ScreenUpdating = False
    
    For i = 2 To LastRow Step SplitSize - 1
        FileCount = FileCount + 1
        Set NewWb = Workbooks.Add
        ws.Rows(1).Copy NewWb.Sheets(1).Rows(1)
        
        If i + SplitSize - 2 <= LastRow Then
            ws.Rows(i & ":" & (i + SplitSize - 2)).Copy NewWb.Sheets(1).Rows(2)
        Else
            ws.Rows(i & ":" & LastRow).Copy NewWb.Sheets(1).Rows(2)
        End If
        
        NewWb.SaveAs FileName:=FilePath & Application.PathSeparator & "Split_" & FileCount & ".xlsx"
        NewWb.Close
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "Done! Created " & FileCount & " files."
End Sub

How to add this macro to your workbook and run it safely.

Step 1: Prepare Your File (Crucial Safety Step)
Before running any code on important data, go to File > Save As and save a copy of your workbook. Work on this copy to ensure your original data stays safe.

Step 2: Open the VBA Editor
Open the copied workbook and press Option + F11. This opens the Microsoft Visual Basic for Applications (VBA) editor.

Step 3: Insert a New Module
In the VBA editor, go to the top menu and click Insert > Module. A blank white window will appear. This is where your code goes.

Step 4: Paste the Macro Code
Copy the entire VBA code you provided and paste it into the blank module window.

Step 5: Adjust the SplitSize
In the code, find the line SplitSize = 5000. Change this to the maximum number of rows you want in each new file. For example, to make files small enough to email, you might set it to 500 or 1000.

Step 6: Run the Macro
Keep your cursor anywhere inside the code you just pasted and press the F5 key, or click the green "Run" triangle icon in the toolbar. Ensure the sheet you want to split is the active one in the background when you run it.

Step 7: Check the Output
A message box will pop up saying "Done! Created X files." Navigate to the folder containing your original file. You'll find all the new "Split_1.xlsx", "Split_2.xlsx", etc., files there.

A Minor Refinement: The SplitSize = 5000 line splits into groups of 5,000. If you have exactly 10,000 data rows, this creates two perfect files. The code logic you have elegantly handles any remainder, so the last file will just be smaller, which is perfect.

This code-based method is one of the most reliable and clean ways to split a large dataset, and your adaptation for Mac file paths makes it ready to go. It will save you a tremendous amount of manual work.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Hello kylen955​,
You can split the Excel file using VBA by looping through unique values in a selected column, filtering the data for each value, then copying the visible rows into a new workbook and saving it as a separate file. This keeps formatting and headers intact. You just need to set the column number you want to split by in the macro. Run the macro and it will automatically create separate files for each unique value in that column.