Working with large master datasets—like sales records, inventory lists, or employee rosters—often requires separating the data into individual tabs based on specific categories like Region, Department, or Sales Rep.

In this guide, you will learn how to use a clean Excel VBA macro that automatically filters your master sheet and splits the rows into dedicated worksheets based on unique values in a chosen column with just one click.

---

Why Automate Data Splitting with VBA?

Manually filtering and copying data into new sheets is repetitive and error-prone. Automating this task provides key advantages:

• Dynamic Tab Creation: Automatically creates new worksheets for unique values (e.g., North, South, East, West) if they don’t already exist.
• Preserves Formatting & Headers: Ensures every newly generated tab carries over the exact column headers from the master table.
• Instant Execution: Processes thousands of rows across dozens of unique categories in seconds.

---

Step 1: Open the VBA Editor Window

1. Open your Excel workbook containing the master dataset.
2. Press Alt + F11 (Option + F11 on Mac) to open the Visual Basic Editor.
3. In the top menu, click Insert ➔ Module to open a clean code module.

---

Step 2: Copy and Paste the Data Split VBA Code

Copy the code block below and paste it directly into your module window:

Sub SplitDataIntoSheets()
    Dim masterWs As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long
    Dim splitCol As Long
    Dim uniqueVals As Collection
    Dim cellVal As Variant
    Dim i As Long
    Dim val As Variant
    
    ' Set master worksheet
    Set masterWs = ActiveSheet
    
    ' Column index to split by (Column A = 1, Column B = 2, Column C = 3, etc.)
    splitCol = 1 
    
    ' Find last row of data
    lastRow = masterWs.Cells(masterWs.Rows.Count, splitCol).End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No data found to split!", vbExclamation, "Excel Owl Automation"
        Exit Sub
    End If
    
    ' Optimize performance
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Extract unique categories
    Set uniqueVals = New Collection
    On Error Resume Next
    For i = 2 To lastRow
        cellVal = masterWs.Cells(i, splitCol).Value
        If cellVal <> "" Then
            uniqueVals.Add cellVal, CStr(cellVal)
        End If
    Next i
    On Error GoTo 0
    
    ' Loop through unique categories and create sheets
    For Each val In uniqueVals
        ' Check if sheet already exists, delete if necessary
        On Error Resume Next
        Worksheets(CStr(val)).Delete
        On Error GoTo 0
        
        ' Add new sheet
        Set newWs = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        newWs.Name = CStr(val)
        
        ' Filter and copy data from Master
        masterWs.Range("A1").AutoFilter Field:=splitCol, Criteria1:=val
        masterWs.UsedRange.SpecialCells(xlCellTypeVisible).Copy newWs.Range("A1")
        
        ' Auto-fit columns
        newWs.Columns.AutoFit
    Next val
    
    ' Clear filter and restore settings
    masterWs.AutoFilterMode = False
    masterWs.Activate
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox uniqueVals.Count & " sheets created successfully!", vbInformation, "Excel Owl Automation"
End Sub

---

Step 3: Run the Macro Using Alt + F8

Run your data-splitting script whenever you update your master list:

1. Switch back to your main Excel window on the master sheet.
2. Press Alt + F8 (or Option + F8 on Mac) to open the Macro dialog.
3. Select SplitDataIntoSheets from the list and click Run.

💡 Pro Tip: Target a Different Column
By default, this script splits data using Column A (splitCol = 1). To split by Column B or C instead, simply change splitCol = 2 or splitCol = 3 in line 13 of the code.

---

Step 4: Save as Macro-Enabled Workbook (.xlsm)

Make sure to save your file in the macro-enabled format to keep your script intact:

1. Click File ➔ Save As (or press F12).
2. In the Save as type menu, choose Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

⚠️ Important Note: Standard .xlsx files do not support macro code. Saving as .xlsx will erase your script permanently!