How can one "split" an Excel (.xlsx) file that contains multiple sheets into separate sheet[n].xlsx?

Question

I think the title says it all for this question but to elaborate a bit further:

I have a .xlsx file which contains a few dozen sheets. I want to output all of those sheets as separate .xlsx files. Automated naming of them isn't necessary. Does excel have a function to export sheets to a separate file?

Answer 1

It is not a built-in feature.

However, if you run this code, it should do the job.

Sub SaveSheets()
    Dim strPath As String
    Dim ws As Worksheet

    Application.ScreenUpdating = False

    strPath = ActiveWorkbook.Path & "\"
    For Each ws In ThisWorkbook.Sheets
        ws.Copy
        'Use this line if you want to break any links:
        BreakLinks Workbooks(Workbooks.Count)
        Workbooks(Workbooks.Count).Close True, strPath & ws.Name & ".xlsx"
    Next

    Application.ScreenUpdating = True
End Sub

Sub BreakLinks(wb As Workbook)
    Dim lnk As Variant
    For Each lnk In wb.LinkSources(xlExcelLinks)
        wb.BreakLink lnk, xlLinkTypeExcelLinks
    Next
End Sub

To run the code, do the following:

1. Open the VBA editor (Alt+F11)
2. In the tree in the top left corner, right click on your workbook and insert a new module
3. Copy the above code into this module
4. Close the VBA editor
5. In Excel press Alt+F8 to run macros and select SaveSheets

or see How do I add VBA in MS Office?

Answer 2

1. When you right-click the tab of an Excel sheet, you can select Move or Copy...

   

2. In the resulting dialog, you can select a target work book. Select (new book).

   

3. Click OK. Your sheet is now inside a new document.

Answer 3

I tried Peter Albert’s solution and it didn’t work for me, so I found a solution in this post (“Excel – save worksheets as separate files”) at Diary of a computer geek.

It works great. You should rename sheets that contain dots to get correctly named files with .xls extensions.

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

Use instructions to create and run this macro from Peter Albert’s post or from How do I add VBA in MS Office?