Split Excel Files To Upload - Too Many Records in a Single File : Helpdesk

Introduction

Sometimes your file could be too big to upload. This article is here to help you out.

How to split Excel Files on Windows
How to split Excel Files on Apple OSX

How to split Excel Files on Windows

To split Excel files on Windows, you'll need to run a piece of VBA code. If this sounds daunting, don't worry, we'll walk you through the process. Please note that these steps may vary slightly based on Windows and Excel versions.

Open your Excel file;
Keep the code snippet below handy;
Select the sheet that holds your data, and remember its name;
Hold the left Alt key and press the F11 key while holding;
This will give you a Microsoft Visual Basic for Applications window.
Go to Insert -> Module
A module window now appears. Copy the code snippet below and paste it in the module window.
In the code snippet, change the value "Sheet1" with the exact name of your sheet (see step 3). Make sure to keep the quotation marks around the value. For example, if we have a file with the sheet name "all-data", we would change Sheet1 to all-data so that the line looks like this:
mainSheetName = "all-data"
You're now set to go - press the Run button (green "Play" icon).
After the VBA script is done you should have a file of 1000 rows for every 1000 rows in your main sheet, stored in the same folder as your Excel file.

Sub Demo()

    Dim mainSheet As Worksheet
    Dim newSheet As Worksheet
    Dim j As Long, numBatches As Long, numRows As Long, batchSize As Long

    mainSheetName = "Sheet1"
    batchSize = 1999
    startRow = 2

    Set mainSheet = ThisWorkbook.Sheets(mainSheetName)
    numRows = Cells(Rows.Count, "A").End(xlUp).Row
    'numBatches = numRows / batchSize
    numBatches = Application.WorksheetFunction.RoundUp(numRows / batchSize, 0)
    batchIterator = 1
    
    For j = 1 To numBatches
        If (j = 1) Then
            batchMin = startRow
        Else
            batchMin = batchMax + 1
        End If
        
        batchMax = batchMin + batchSize - 1
        
        sheetName = mainSheetName & "_" & batchMin & "-" & batchMax
        Sheets.Add.Name = sheetName
        Set newSheet = ThisWorkbook.Sheets(sheetName)
        
        mainSheet.Rows(1).EntireRow.Copy newSheet.Range("A" & 1) 'Copy Header Row
        mainSheet.Range("A" & batchMin & ":A" & batchMax).EntireRow.Copy newSheet.Range("A2") 'Copy Data Row
        
    Next j
    
    Dim filePath As String
    filePath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each batchSheet In ThisWorkbook.Sheets
        If (batchSheet.Name <> mainSheetName) Then
            batchSheet.Copy
            Application.ActiveWorkbook.SaveAs Filename:=filePath & "\" & batchSheet.Name & ".xlsx"
            Application.ActiveWorkbook.Close False
            ThisWorkbook.Sheets(batchSheet.Name).Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

How to split Excel Files on Apple OSX

To split Excel files on Apple, you'll need to run a piece of VBA code. If this sounds daunting, don't worry, we'll walk you through the process. Please note that these steps may vary slightly based on OSX and Excel versions.

Close all other Excel files to avoid confusion;
Open your Excel file;
Keep the code snippet below handy;
Select the sheet that holds your data, and remember its name;
Go to Excel -> Preferences...;
Go to "Ribbon & Toolbar" icon;
In Quick Access Toolbar, choose the "Developer tab" from the drop-down;
Add "Visual Basic" to the Quick Access Toolbar, Save, and close the Preferences window;
Click the Visual Basic icon that now appears at the top-left corner of Excel;
This will give you a Microsoft Visual Basic for Applications window;
Expand the VBAProject with the same name as your file, and then select your main sheet;
A module window now appears. Copy the code snippet below and paste it in the module window.
In the code snippet, change the value "Sheet1" with the exact name of your sheet (see step 3). Make sure to keep the quotation marks around the value. For example, if we have a file with the sheet name "all-data", we would change Sheet1 to all-data so that the line looks like this:
mainSheetName = "all-data"
You're now set to go - press the Run button (this looks similar to a play button);
You'll be prompted to allow access to the folder that your file is situated in. Once you do so, Excel could crash (don't worry, just restart Excel and start again).
After the VBA script is done you should have a file of 1000 rows for every 1000 rows in your main sheet, stored in the same folder as your Excel file.

Sub Demo()

    Dim mainSheet As Worksheet
    Dim newSheet As Worksheet
    Dim j As Long, numBatches As Long, numRows As Long, batchSize As Long

    mainSheetName = "Sheet1"
    batchSize = 1999
    startRow = 2

    Set mainSheet = ThisWorkbook.Sheets(mainSheetName)
    numRows = Cells(Rows.Count, "A").End(xlUp).Row
    'numBatches = numRows / batchSize
    numBatches = Application.WorksheetFunction.RoundUp(numRows / batchSize, 0)
    batchIterator = 1
    
    For j = 1 To numBatches
        If (j = 1) Then
            batchMin = startRow
        Else
            batchMin = batchMax + 1
        End If
        
        batchMax = batchMin + batchSize - 1
        
        sheetName = mainSheetName & "_" & batchMin & "-" & batchMax
        Sheets.Add.Name = sheetName
        Set newSheet = ThisWorkbook.Sheets(sheetName)
        
        mainSheet.Rows(1).EntireRow.Copy newSheet.Range("A" & 1) 'Copy Header Row
        mainSheet.Range("A" & batchMin & ":A" & batchMax).EntireRow.Copy newSheet.Range("A2") 'Copy Data Row
        
    Next j
    
    Dim filePath As String
    filePath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each batchSheet In ThisWorkbook.Sheets
        If (batchSheet.Name <> mainSheetName) Then
            batchSheet.Copy
            Application.ActiveWorkbook.SaveAs Filename:=filePath & "/" & batchSheet.Name & ".xlsx"
            Application.ActiveWorkbook.Close False
            ThisWorkbook.Sheets(batchSheet.Name).Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Discover. Learn. Grow.