How to export and save each worksheet as new workbook in Excel?
Let's say you want to export and save one or several worksheets as a new workbook, how will you deal with it? Normally you may copy each worksheet and paste to new workbook. But here we bring you some handy tricks to deal with it:
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.
Step 1: Select the worksheet names in tab bar. You can select multiple with holding down Ctrl key or shift key.
Step 2: Right click the worksheet name, and click the Move or Copy from context menu.
Step 3: In Move or Copy dialog box, select the (new book) item from the drop down list of Move selected sheets to book.
Step 4: Then click OK, now you have switched to the new workbook with exported or copied worksheets, click File > Save to save the new workbook.
Note: In the Move or Copy dialog box, there is a Create a copy option. If you do not check it, it will move the selected worksheets out of original workbook; if check it, it will copy selected worksheets.
This following code will export every visible worksheet in a new workbook and save the workbook with the name of the original sheet in a newly created folder in the same path as the active workbook. Please do as following steps:
Step 1: Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
Step 2: Click Insert > Module, and paste the following macro in the Module Window:
VBA : Export and save worksheets as new workbook in a new folder.
Sub SplitWorkbook() 'Updateby20140612 Dim FileExtStr As String Dim FileFormatNum As Long Dim xWs As Worksheet Dim xWb As Workbook Dim FolderName As String Application.ScreenUpdating = False Set xWb = Application.ThisWorkbook DateString = Format(Now, "yyyy-mm-dd hh-mm-ss") FolderName = xWb.Path & "\" & xWb.Name & " " & DateString MkDir FolderName For Each xWs In xWb.Worksheets xWs.Copy If Val(Application.Version) < 12 Then FileExtStr = ".xls": FileFormatNum = -4143 Else Select Case xWb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If Application.ActiveWorkbook.HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum Application.ActiveWorkbook.Close False Next MsgBox "You can find the files in " & FolderName Application.ScreenUpdating = True End Sub
Step 3: Press the F5 key to run this code. And a prompt box will pop up to tell you the location of the new exported workbooks, and all of the worksheets of the original workbook have been exported to some new separate workbooks which named original sheets in a new specific folder. See screenshots:
If you have multiple worksheets needed to be saved as separate workbook, the first method cannot be a good choice. And the VBA code maybe a little complicated for Excel beginners. Here you can use the Split Workbook of Kutools for Excel to quickly and easily export and save each worksheet of one workbook to separate new workbook.
Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Get it Now.
After free installing Kutools for Excel, please do as below:
Step 1: Click Enterprise > Workbook > Split Workbook…. See screenshot:
Step 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:
Step 3: Then click Split button, and choose the folder that you want to put the new workbooks to. See screenshot:
Step 4: And then click OK, then each checked worksheet in the Split Workbook dialog box is exported and saved as individual workbook. Each new workbook is named with the original worksheet name. See screenshot:
- Combine multiple workbooks to single workbook
- Save a selection as individual file
- Split a workbook to separate Excel files
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...