How to split a workbook to separate Excel files in Excel?
You may need to split a large workbook to separate Excel files with saving each worksheet of the workbook as an individual Excel file. For example, you can split a workbook into multiple individual Excel files and then deliver each file to different person to handle it. By doing so, you can get certain persons handle specific data, and keep your data safe. This article will introduce ways to split a large workbook to separate Excel files based on each worksheet.
Classic Menu for Office: Bring Back Old Look to Office 2010 and 2013 (includes Office 365).
Office Tab: Bring Tabbed Editing and Browsing Features to Office, Just Like Chrome, Firefox, Internet Explorer 8/9/10.
In usual, using Copy command and Paste command can save a workbook as a Separate Excel file manually. Firstly, select the whole worksheet that you want to save as a separate file, create a new workbook, and then paste it in the new workbook, at the end saves it.
This way is easy-to-use if you need to split only a few worksheets as separate files. However, it must be time-consuming and tedious to split many worksheets with copying and pasting manually.
The following VBA code can help you quickly split multiple worksheets of current workbook to separate Excel files, please do as follows:
1. Create a new folder for the workbook that you want to split, because the split Excel files will be stayed at the same folder as this master workbook.
2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
Filename:=MyPath & "\" & sht.Name & ".xls"
4. Press the F5 key to run this code. And the workbook have been split to separate Excel files in the same folder as the original workbook. See screenshot:
If you have Kutools for Excel installed, its Split Workbook tool can split multiple worksheets as separate Excel files conveniently and quickly with only a few clicks.
1. After installing Kutools for Excel, click Enterprise > Workbook Tools > Split Workbook , see screenshot:
2. In the Split Workbook dialog box, all of the worksheet names have been checked by default, if you don’t want to split some of the worksheets, you can uncheck them. (If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets.) See screenshot:
3. Then click Split button, in the popping up dialog box, choose a location for the new Excel files.
4. And then click OK, the checked worksheets will be saved as new separated workbooks quickly. Each new workbook is named with the original worksheet name. See screenshot:
Kutools for Excel's Split Workbook tool makes it easy to split active workbook into individual Excel files (one file contains one worksheet), and each Excel file carries the name of the worksheet. You can set to skip all the blank and hidden worksheets. Read more…