How to split Excel file into multiple files?

In general thousands of rows is not "very large", depends on which formulae do you use inside. Perhaps it's worth to check which techniques do you use and is it way for improvement.

Unless Olufemi7​ and NikolinoDE​ are mind-readers, I don't see how they (or anybody) can give you a valid recommendation without knowing more about the nature of your "very large Excel workbook" with its thousands of rows. So much would depend on whether those rows are transactions that have taken place over multiple years, history that you might want to keep for reference and comparison purposes, for example, or are they thousands of different iterations of product codes that are all current. In the latter case (or comparable) you could split by categories of products; in the former, perhaps think about how much live access you need to ancient (5  years or more?) history.... and so forth.

Is the file large because many items on each row are in the form of formulas, whereas now you could convert them to values and not require recalculation each time something is done?

Lots of questions....if NikolinoDE​ did manage to read your mind correctly, I commend him and his solution. But if you're still seeking a workable solution, may I suggest you help us help you by giving some more descriptive details (no need to disclose confidential info) on the nature of your workbook.

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.