16

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?

16

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?

  • Thanks! What about the reverse?--to put it back together? This excel file apparently contained "links" (I haven't used this feature before), and after the splitting Excel can't find the links (it's looking for the first sheet); is there away to split them and update the links at the same time/or just update the links? – eichoa3I Mar 6 '13 at 23:59
  • 1
    the question is how you want to handle the links. You can easily replace them with values by inserting Workbooks(Workbooks.Count).BreakLinks after ws.Copy... – Peter Albert Mar 7 '13 at 8:36
  • After I opened one workbook a pop up window in Excel prompted me to fix the links; I clicked through this dialog box and now the links work for all files. I'm wondering though if this was saved in the file or locally... – eichoa3I Mar 7 '13 at 15:12
  • When you say replace them with values with Workbooks(Workbooks.Coun).BreakLinks do you mean the links are deleted, or ...? I think the best way to handle them is simply to do away with them (i.e., when users open the now separated excel files, they don't see the warning about broken links). – eichoa3I Mar 7 '13 at 17:55
  • sorry, try Workbooks(Workbooks.Count).BreakLink - without the s at the end – Peter Albert Mar 7 '13 at 23:23
11
  1. When you right-click the tab of an Excel sheet, you can select Move or Copy...

    enter image description here

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

    enter image description here

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

7

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?

  • 1
    I also tried Peter Albert's solution and there was an error "Run-time error'13': Type mismatch. Luckily the solution in this answer works for me. – Bin Jan 27 '16 at 16:59
  • how can i get it to overwrite the existing files with the same name? – DAE Nov 9 '17 at 8:34
  • I did not need this because each dump goes into a separate folder. Maybe try this thread – hrvoj3e Nov 10 '17 at 12:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.