How to use Split Workbook Wizard for Excel
Split Workbook Wizard lets you quickly copy all or selected worksheets to new Excel workbooks. You can select the worksheets which you want to place in the resulting workbooks, move every specified number of sheets to a separate file, or group worksheets in new workbooks by substrings.
Before you start
If the worksheet you're going to place in a new workbook has references to other worksheets, make sure that the new workbook contains the worksheets with the source data.
Macros are not copied to new workbooks.
How to split a workbook in Excel
Here is how you can use the three-step Split Workbook Wizard to copy worksheets to new workbooks.
Start Split Workbook Wizard
Open the workbook which you want to split. On the Ablebits Tools tab, in the Transform group, click Split Workbook:
The Split Workbook Wizard allows you to:
- Manually select the worksheets you want to turn into separate workbooks.
- Group worksheets in new workbooks by substrings in their names.
- Copy every specified number of sheets to a new workbook.
Place each selected sheet in a separate workbook
Step 1: Specify how to split your workbook
If you want to place only specific sheets in separate workbooks, select the first option—Place each selected sheet in a separate workbook:
Click Next to proceed to the next step.
Step 2: Choose sheets to be placed in separate files
In the list of worksheets, select the ones that will be placed in new workbooks.
Tip. Click the New workbooks checkbox to select or deselect all worksheets in the list.
If you have an extensive list of worksheets, take advantage of the Filter field. Enter the characters that should appear in a sheet name, and the worksheets whose names do not contain those characters will be filtered out.
Click Next to continue.
Step 3: Choose how to save the new workbooks
To select the location for the newly created workbooks, click Browse, and then select the destination folder for the new files in the Browse For Folder dialog window.
You will see the path in the Save the new workbooks to field.
Note. The new workbook will have the same name as the worksheet copied to it.
In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook. To have this done automatically, keep the corresponding checkbox below the Save the new workbooks to field selected.
With this option checked, your new workbook will be named like this:
If you clear this checkbox and a workbook with the same name is currently open, you’ll be prompted to either Auto-rename the new workbook with a timestamp or Skip, in which case the new workbook will not be created.
If this checkbox is cleared and a workbook with the same name already exists in the destination folder, you will be provided with the following choices:
- Auto-rename—a timestamp will be added to the name of the new workbook.
- Overwrite—the existing workbook will be deleted and a new one with the same name will be created.
- Skip—the new workbook will not be created.
Tip. To apply your choice to all other new workbooks with the same names, select the Do the same with the other workbooks checkbox.
Click Split to have the new workbooks created.
Group worksheets by substrings
Step 1: Specify how to split your workbook
If you want to group worksheets in new workbooks according to certain text/characters included in their names, select the second option—Group worksheets by substrings.
Click Next.
Step 2: Specify substrings to group your worksheets by
-
In the Enter substrings field, type the substring(s) by which the worksheets will be grouped in new workbooks.
Note. To have several groups of worksheets placed in different workbooks, type each substring in a new line.Note. The substrings you enter will be used as the names for the new workbooks. -
In the Result preview field, you can view the resulting workbook(s) and the worksheet(s) they will contain.
Clear the checkboxes next to the names of the worksheets which you don’t want to be copied to new workbooks.Tip. Click New workbooks to select or deselect all.
Click Next to continue.
Step 3: Choose how to save the new workbooks
Choose the location for the newly created workbooks.
Click Browse and, in the Browse For Folder dialog window that will pop up, select the destination folder for the new files. The path will appear in the Save the new workbooks to field.
Note. The new workbooks will be named by the substrings you enter in Step 2.
In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook. To have timestamps added automatically, select the corresponding checkbox below the Save the new workbooks to field.
If this checkbox is cleared, you'll be offered to either Auto-rename the new workbook using a timestamp or Skip, in which case the new workbook will not be created. For full details, please see Step 3 of the previous section.
Click Split to have the new workbooks created.
Split the workbook by every N sheets
Step 1: Specify how to split your workbook
If you aim to place every specified number of sheets in a new workbook, select the third option—Split the workbook by every N sheets.
Click Next.
Step 2: Decide how many worksheets to bring to new workbooks
-
In the Split by every N sheets field, type the number of worksheets to place in every new workbook (or use the up and down arrows to set the number).
-
In the list of new workbooks, you can view the resulting workbooks with the worksheets they will contain. Clear the checkboxes next to the names of the worksheets which you don’t want to be placed in new workbooks.
Tip. Click New workbooks to select or deselect all.
Click Next.
Step 3: Choose how to save new workbooks
Choose the location for the new workbooks.
Click Browse and in the Browse For Folder dialog window that opens, select the destination folder for the new files. You will see the path in the Save the new workbooks to field.
Note. The new workbooks will be named as Book1.xlsx, Book2.xlsx, etc.
In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook.
If you want timestamps to be added to the names of the workbooks automatically when the workbook with the same name already exists in the destination folder or is open, select the checkbox below the Save the new workbooks to field.
If this checkbox is cleared, you'll be offered to either Auto-rename the new workbook using a timestamp or Skip, in which case the new workbook will not be created. For full details, please see Step 3 of the Place each selected sheet in a separate workbook section.
Click Split.
Note. Formatting might be lost if the workbook that is going to be split has the following file format: Excel 97 - Excel 2003 Workbook (.xls).
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
