How to export and save each worksheet as separate 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:
Save worksheets as new workbook one by one with Move or Copy command
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.
Easily batch save worksheets as new workbook with Kutools for Excel
If you have multiple worksheets needed to be saved as separate workbook, the first method is not a good choice. And the VBA code below maybe a little complicated for Excel beginners. Here you can use the Split Workbook utility of Kutools for Excel to easily batch save each worksheet of one workbook to separate new workbook.
After installing Kutools for Excel, click Kutools Plus > Workbook > Split Workbook to open the Split Workbook dialog box. And then configure as follows.
- Check the worksheets you want to save as new workbooks. By default, all worksheets are checked.
- It is optional to skip hidden and blank worksheets by checking the corresponding checkboxes in the Options section.
- Click OK.
- A Select Folder dialog box pops up, you then need to select a destination folder to save the new workbooks and click the Select Folder button.
Then, the specified worksheets or all worksheets will be saved as separate new workbooks immediately.
- This feature allows you to save each worksheet as individual TXT, CSV or PSF file. You just need to check the Save as type checkbox and choose the corresponding option you need from the drop-down.
- To use this feature, you should have Kutools for Excel installed on your computer. Go to download Kutools for Excel to get a 30-day free trial with no limitations.
Export and save worksheets as new workbook with VBA code
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()
'Updateby20200806
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim xNWb 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
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
MkDir FolderName
For Each xWs In xWb.Worksheets
On Error GoTo NErro
If xWs.Visible = xlSheetVisible Then
xWs.Select
xWs.Copy
xFile = FolderName & "\" & xWs.Name & FileExtStr
Set xNWb = Application.Workbooks.Item(Application.Workbooks.Count)
xNWb.SaveAs xFile, FileFormat:=FileFormatNum
xNWb.Close False, xFile
End If
NErro:
xWb.Activate
Next
MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End SubStep 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:
Export and save each sheet as new workbook
Related articles:
- Combine multiple workbooks to single workbook
- Save a selection as individual file
- Split a workbook to separate Excel files
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
