Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 8 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

or see How do I add VBA in MS Office?

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 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?

share|improve this answer
    
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 at 16:59

Simple Excel VBA macro that creates a separate CSV file (with sheet name) for each sheet of workbook.

http://gethowstuff.com/vba-macro-save-each-sheet-workbook-to-seperate-csv-files/

Sub saveSheetsSeperateCSV()
    Dim sheetsCount
    sheetsCount = 1
    Dim activeSheetName
    Do
        If ActiveSheet.Index = Worksheets.Count Then
            Worksheets(1).Select
        Else
            ActiveSheet.Next.Select
        End If
        activeSheetName = ActiveSheet.Name
        ActiveWorkbook.SaveAs Filename:="C:\test\" & activeSheetName & ".csv", _
                                FileFormat:=xlCSV, CreateBackup:=False
        sheetsCount = sheetsCount + 1
    Loop Until sheetsCount > Worksheets.Count
End Sub
share|improve this answer
1  
Why is this being downvoted? – Frankie Jun 11 '15 at 22:28
    
It’s OK to compose your posts in Microsoft Word (or some comparable tool) and then paste them into Super User; I do it all the time.  OK, that is, except for the fact that Word “autocorrects” many things, e.g., 1/2 → ‘½’ and "straight quotes"“curly quotes”.  These are often nice to have in text, but these characters are typically unacceptable in code.  When you post code, please ensure that it is free of special characters that will cause an error when the code is copied and pasted out (e.g., into the VBA editor). – G-Man Jun 11 '15 at 22:30
    
@Chipperyman it is down voted because it was flagged as spam. While it is almost spam it is reasonably obvious that the user is affiliated with the blog it came from and as long as they don't try to mention their site in every post it should be fine. – Mokubai Jun 11 '15 at 22:37
    
@gethowstuff, just so you know our site rules on promoting your site, you should check out our help center pages, specifically superuser.com/help/promotion . Thank you for taking the time to actually post your content here rather than just posting a link (which would have been deleted as spam). – Mokubai Jun 11 '15 at 22:42

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.