How do I split an Excel sheet into separate files with VBA?

Collect the unique values of the split column into a Dictionary, then loop over them: filter the data by each value, copy the visible rows into a new workbook and save it with that value as the file name.

VBA macro: split an Excel sheet into separate files by column value

Most macros you find for this add a worksheet per value inside the same workbook. If what you need is one saved .xlsx per region, per client or per supplier, here is a routine that does exactly that — and an honest list of what it still leaves you to do by hand.

The macro

Press Alt+F11, then Insert → Module, and paste both procedures. Set splitCol to the number of the column you want to split by — column B is 2, column D is 4.

Sub SplitToFilesByColumn()
    Dim wsData As Worksheet, wsNew As Worksheet, wbNew As Workbook
    Dim lastRow As Long, lastCol As Long, i As Long
    Dim splitCol As Long, savePath As String, n As Long
    Dim dict As Object, key As Variant

    splitCol = 2                                   ' <-- column to split by
    Set wsData = ActiveSheet
    savePath = ThisWorkbook.Path & "\split\"

    lastRow = wsData.Cells(wsData.Rows.Count, splitCol).End(xlUp).Row
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Then MsgBox "No data found.": Exit Sub
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath

    ' 1. collect the unique values of the split column
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 2 To lastRow
        key = Trim(CStr(wsData.Cells(i, splitCol).Value))
        If Len(key) > 0 Then dict(key) = 1
    Next i

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 2. one filtered copy per value, saved as its own workbook
    For Each key In dict.Keys
        Set wbNew = Workbooks.Add(xlWBATWorksheet)
        Set wsNew = wbNew.Worksheets(1)

        If wsData.AutoFilterMode Then wsData.AutoFilterMode = False
        wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol)) _
               .AutoFilter Field:=splitCol, Criteria1:=key

        wsData.Rows(1).Copy wsNew.Rows(1)
        On Error Resume Next
        wsData.Range(wsData.Cells(2, 1), wsData.Cells(lastRow, lastCol)) _
               .SpecialCells(xlCellTypeVisible).Copy wsNew.Cells(2, 1)
        On Error GoTo 0

        wbNew.SaveAs savePath & SafeFileName(CStr(key)) & ".xlsx", xlOpenXMLWorkbook
        wbNew.Close SaveChanges:=False
        n = n + 1
    Next key

    If wsData.AutoFilterMode Then wsData.AutoFilterMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox n & " files saved in " & savePath
End Sub

Function SafeFileName(s As String) As String
    Dim bad As Variant, ch As Variant
    bad = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    For Each ch In bad
        s = Replace(s, ch, "_")
    Next ch
    SafeFileName = Left(Trim(s), 100)
End Function

Save your workbook as .xlsm first — a plain .xlsx cannot hold macros.
Select the sheet with the data, with headers in row 1.
Press F5 inside the editor. The files appear in a split subfolder next to your workbook.

⚠ Run it on a copy the first time. The macro turns filters on and off on your live sheet. Nothing is deleted, but a dry run on a duplicate is cheap insurance.

What the macro does not solve

The routine above is the easy half. Three things stay on your plate, and they are the reason this job keeps taking a morning.

Gap	What it means in practice
It does not email anything	You still attach twenty files to twenty near-identical messages, each to a different address. Adding Outlook automation on top is a second project, and a fragile one.
Layout is only partly carried over	Cell formatting travels with the copied rows. Column widths, frozen panes and conditional formatting rules do not, unless you write extra code for each.
Macros are often blocked	Many companies block macros in files received from outside. The colleague you pass the workbook to may simply not be able to run it.

There is also the maintenance: when someone inserts a column, splitCol = 2 silently points at the wrong data, and the macro keeps running without complaining.

When VBA is the right answer anyway

If the split runs unattended on a schedule, sits inside a larger macro you already maintain, or has to happen on a machine with no internet access, VBA is the correct tool and the code above is a reasonable starting point.

When it is not

If you are writing this macro because you have to do the same split every week and then send each part to a different person, you are about to build the easy half and keep the tedious half. Split & Send does both in one pass: pick the column, get one formatted file per value and a ready-to-send email draft for each — right recipient, message written once, file already attached. It runs in the browser, needs no macros and no install, and your file is never uploaded anywhere.

Nothing is sent automatically: the drafts land in your mail client and you review them before they go.

Try it with your file — free, no sign-up →

Frequently asked questions

Why does my macro create sheets instead of files?

Most examples online add a worksheet per value inside the same workbook. To get separate files you need a new Workbook object for each value and a SaveAs call, as in the routine above.

Does the macro keep the original formatting?

Partly. Copying rows carries cell formatting with them, but column widths, frozen panes, conditional formatting and the general sheet layout are not reproduced unless you copy those properties explicitly.

Can the macro email each file to a different person?

Not on its own. You would add Outlook automation on top — a reference to the Outlook object model, plus code mapping every value to an address. It works, but it is a second project to write and to maintain.

What if macros are blocked at my company?

That is common, and it usually surfaces when you hand the workbook to a colleague. A browser-based tool needs no macros at all, so nothing has to be unblocked.

Can I split by more than one column?

With VBA, yes: build the dictionary key by joining the two cell values with a separator, and use two filter criteria in the loop. It is a small change to the code above.