Glam Prestige Journal

Bright entertainment trends with youth appeal.

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?

3 Answers

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?

6

  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.

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?

5

Related Journals