How to split one excel sheet into multiple files

Files imported to a nation need to be smaller than 1000 MB, and your computer may have trouble opening a CSV file that is larger than 50 MB, in this case, you may need some additional help with file splitting. So, Microsoft MVP in Excel-Bill Jelen created a solution just for NationBuilder customers: The File Splitter!

Bill graciously provided permission for NationBuilder to share both the Excel file and the macro code. This is shared as a use at your own risk, unsupported tool. 

This macro works on an open Excel file. The interface for the file is:

To run the macro, fill in the following information:

• The number of heading rows (Cell C6)

• The number of files to be created (Cell C9)

Make sure macros are enabled and click Control-Shift-S. Once the file split is finished, the macro will return the location of the files, the number of rows in the file and the total number of files created.

Click here to download a compressed version of the Excel File Splitter. After unzipping the file, you'll be able to open it in Excel. You'll see a warning message that the workbook contains macros and that macros can contain viruses. In order to use the macro, click the Enable Macros button. 

If you would prefer to build the macro yourself, use this process to add the code to a file:

• Open a new file in Excel

• Enable macros

• Open the macro editor

• Copy the text below starting at "Sub" and ending with "End Sub"

• Paste it into the macro editor

• Return to Excel from the macro editor

• Save the file as a file of type .xlsm

Here is the code for the macro:

Sub SplitTheWorkbook()
Dim WBT As Workbook
Dim WBO As Workbook
Dim WBN As Workbook
Dim WST As Worksheet
Dim WSO As Worksheet
Dim WSN As Worksheet
Set WBT = ThisWorkbook
Set WST = WBT.Worksheets(1)
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet
TopCount = WST.Range("C6").Value
DivCount = WST.Range("C9").Value
If WBT.Name = WBO.Name Then
MsgBox "Please switch to the large workbook before Ctrl+Shift+S"
Exit Sub
End If
MyName = WBO.Name
MyPath = WBO.Path & Application.PathSeparator
WST.Range("E17").Value = MyPath
MyRows = WSO.UsedRange.Rows.Count
WST.Range("E18").Value = MyRows
RowsPerFile = Int(MyRows / DivCount) + 1
WST.Range("E19").Value = RowsPerFile
StartRow = TopCount + 1
Ctr = 0
For i = StartRow To MyRows Step RowsPerFile
Ctr = Ctr + 1
NewFN = MyPath & Format(Ctr, "000") & MyName
Application.StatusBar = NewFN & " rows " & i & " to " & (i + RowsPerFile - 1)
WSO.Copy
Set WBN = ActiveWorkbook
Set WSN = ActiveSheet
KeepRow1 = i
KeepRowN = i + RowsPerFile - 1
DelRow1 = KeepRowN + 1
' Delete everything from DelRow1 on down
DelSize1 = 1048576 - DelRow1
Cells(DelRow1, 1).Resize(DelSize1, 1).EntireRow.Delete
If i > StartRow Then
' Also delete rows at the top
DelRow1 = StartRow
Range(Cells(StartRow, 1), Cells(i - 1, 1)).EntireRow.Delete
End If
WBN.SaveAs NewFN, FileFormat:=WBO.FileFormat
WBN.Close False
WST.Cells(20 + Ctr, 2).Value = NewFN
Next i
WBT.Activate
Application.StatusBar = False
MsgBox Ctr & " files created"
End Sub