Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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
share|improve this answer
    
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? –  jhstuckey 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... –  jhstuckey 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). –  jhstuckey 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
  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.

share|improve this answer

I tried Peter Albert's solution and it didn't work for me so i found a solution here: http://jen3ral.wordpress.com/2012/06/29/excel-save-worksheets-as-separate-files/

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

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 post here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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