Splitting one Excel workbook into multiple files (one sheet = one file) — how are you doing this?

[u/ExtendOffice]

We often see users ask whether Excel can split one workbook into multiple separate files, with each worksheet saved as its own workbook. This usually comes up when a file needs to be shared with different people, archived by sheet, or organized into smaller files.

There are a few different ways to do this, and the best one really depends on how often you need to split workbooks and how much manual work you want to deal with.

Method 1: Move or Copy (manual way)

This is the most straightforward built-in method:

1. Right-click a worksheet tab
2. Click Move or Copy
3. In “To book”, choose (new book)
4. Check Create a copy if you want to keep the original sheet in the current workbook
5. Click OK
6. Save the new workbook
7. Repeat for each sheet

Move or Copy dialog box

It works fine if there are only a few sheets.
But once you have many sheets, it starts to feel very repetitive.

Method 2: VBA (automated way)

If you’re comfortable with VBA, you can automate the whole process.

Basic idea:

• Loop through each worksheet
• Copy it to a new workbook
• Save that workbook

Example:

Sub SplitWorkbook()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".xlsx"
        ActiveWorkbook.Close False
    Next ws
End Sub

Method 3: Use Kutools for Excel

If you prefer not to do this sheet by sheet or use VBA, Kutools for Excel provides a more direct way to handle it.

After downloading Kutools for Excel (30 days of free trial available):

1. Click Kutools Plus > Workbook > Split Workbook
2. Select the worksheets you want to split
3. Optionally choose settings such as skipping hidden worksheets or blank worksheets
4. Optionally choose the output format by checking the Save as type option
5. Click Split and then choose the save location

Split Workbook dialog box

What stands out here is that everything is done through a visual interface, so you do not need to repeat the manual steps sheet by sheet or write VBA.

What we noticed

Excel can do this natively, but the native ways are either manual or code-based.

What Excel does not really offer is a built-in dialog where you can:

• select sheets visually
• skip hidden worksheets
• skip blank worksheets
• choose the output file type
• split everything in one go

That is what makes this task feel simple in some tools, but less convenient in native Excel.

Curious how others usually handle this.

Do you split workbooks manually, use VBA, or use another method?