Contents
I already wrote about the assembly of sheets from several books into one current one here. Now let’s analyze the solution to the inverse problem: there is one Excel workbook that needs to be “parsed”, i.e. save each sheet as a separate file for later use.
There are many examples of this from real life. For example, a report file with branch sheets must be divided into separate books by sheets in order to then transfer data to each branch, etc.
If you do this procedure manually, you will have to perform a rather large chain of actions for each sheet (select a sheet, right-click on the sheet tab, select Copy, specify a separate pre-created empty file, etc.) It’s much easier to use a short macro that automates these steps.
Method 1. Simple separation
Press combination Alt + F11 or choose from the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new module through the menu Insert – Module and copy the text of this macro there:
Sub SplitSheets1() Dim s As Worksheet For Each s In ActiveWorkbook.Worksheets 'Iterate through all the sheets in the active workbook s.Copy 'Copy each sheet to a new file Next End Sub
If we now exit the Visual Basic editor and return to Excel, and then run our macro (Alt + F8), then all sheets from the current book will be divided into separate newly created books.
Method 2. Separation with preservation
If necessary, you can immediately save the created books under the sheet names. To do this, the macro will have to be slightly modified by adding the save command to the loop:
Sub SplitSheets2() Dim s As Worksheet Dim wb as Workbook Set wb = ActiveWorkbook For Each s In wb.Worksheets 'go through all the sheets in the active workbook s.Copy 'save the sheet as a new file ActiveWorkbook.SaveAs wb.Path & "" & s .Name & ".xlsx" 'save file Next End Sub
This macro saves new workbooks to the same folder as the original file. If you need to save to another location, replace wb.Path with your path in quotes, such as “D:Reports2012”, etc.
If you need to save files not in the standard Excel workbook format (xlsx), but in others (xls, xlsm, xlsb, txt, etc.), then in addition to obviously changing the extension to the desired one, you will also need to add a clarification of the file format – parameter FileFormat:
ActiveWorkbook.SaveAs wb.Path & "" & s.Name & ".xlsb", FileFormat:=50
For basic file types, parameter values FileFormat following:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
A complete list of all options can be found in the MSDN help.
Method 3. Save only selected sheets to new workbooks
If you want to scatter among the files not all the sheets in your book, but only some, then the macro will have to be slightly modified. Select the sheets you need in the workbook by holding down the Ctrl or Shift key on your keyboard and run the macro below:
Sub SplitSheets3() Dim AW As Window Set AW = ActiveWindow For Each s In AW.SelectedSheets Set TempWindow = AW.NewWindow 'Create a separate time window s.Copy 'Copy the sheet from the selected range there TempWindow.Close 'Close the time window Next End Sub
It is necessary to create a new window and copy through it, and not directly, because Excel cannot copy a group of sheets if there are sheets with smart tables among them. Copying through a new window circumvents this problem.
Method 4. Save only selected sheets to a new file
In all the methods described above, each sheet was saved to its own separate file. If you want to save a group of previously selected sheets into a separate new file at once, then we will need to slightly modify our macro:
Sub SplitSheets4() Dim CurW As Window Dim TempW As Window Set CurW = ActiveWindow Set TempW = ActiveWorkbook.NewWindow CurW.SelectedSheets.Copy TempW.Close End Sub
Method 5: Save Sheets as Separate PDF Files
This method is somewhat similar to the second one, but the sheets are not saved as separate Excel workbooks, but in PDF format, which is often required if no one should change the document and see your formulas. Note that:
- another method is used for this (ExportAsFixedFormat and not Copy)
- Sheets are output to PDF with the print settings configured on the Page Layout tab
- the book must be saved at the time of export
The code we need will look like this:
Sub SplitSheets5() Dim s As Worksheet For Each s In ActiveWorkbook.Worksheets s.ExportAsFixedFormat Filename:=ThisWorkbook.Path & "" & s.Name & ".pdf", Type:=xlTypePDF Next End Sub
Method 6: Premade macro from the PLEX add-on
If you are too lazy or have no time to implement all of the above, then you can use a ready-made macro from my PLEX add-on:
- Assembling sheets from several books into one
- What are macros, where to insert Visual Basic code, how to use them.