How to export and save sheets and worksheets 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:

Export and save worksheets as new workbook with Move or Copy command

Export and save worksheets as new workbook with VBA code

Export and save worksheets as new workbook with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Export and save worksheets as new workbook with Move or Copy command

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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.

doc-export-worksheet-to-workbook1

Step 3: In Move or Copy dialog box, select the (new book) item from the drop down list of Move selected sheets to book.

doc-export-worksheet-to-workbook2

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.


arrow blue right bubble 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()
'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:

doc-export-worksheet-to-workbook3
-1
doc-export-worksheet-to-workbook8

arrow blue right bubble Export and save worksheets as new workbook with Kutools for Excel

The Kutools for Excel's Split Workbook can save each worksheet separately to multiple workbooks.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Click Enterprise > Split Workbook…. See screenshot:

doc-export-worksheet-to-workbook4

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:

doc-export-worksheet-to-workbook5

Step 3: Then click Split button, and choose the folder that you want to put the new workbooks to. See screenshot:

doc-export-worksheet-to-workbook6

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:

doc-export-worksheet-to-workbook7

Click to know more information about this Split Workbook tool.


Related articles:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+1#Nasser2013-12-07 10:49
thanks alot, works very well and easy.
Reply | Reply with quote | Quote
+1#Sean2014-01-26 12:06
Hello I like the code
Is it possible to just save the new workbook without the date information That is so when I resave the sheets in the previous workbook I will have the option to overwrite the changes rather than create another dated folder? Also but not so important how could I use another name and destination for the destination folder
Regards
Sean
Reply | Reply with quote | Quote
+1#Rajesh Jayagopi2014-02-13 11:05
Thanks a lot. Good Job!.
Reply | Reply with quote | Quote
+1#Keith2014-03-18 20:49
Is there a way to change this macro so I can save it in a file in the XLSTART folder but it will run for the active workbook? Everytime I save it in the XLSTART workbook it fails at the DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")line.

Thanks!
Keith
Reply | Reply with quote | Quote
0#Phani2014-04-07 15:20
Hi All,
Can someone help me on below.
I need VBA code that can copy only all visible sheets to new workbook and it should not copy macro code to new workbook, save new workbook on desktop with two different cell values in sheet and sheet name as a file name and close the master workbook without saving.
Reply | Reply with quote | Quote
0#Votefor Pedro2014-04-17 20:24
Thank you. This is esactly what I needed.
Reply | Reply with quote | Quote
0#Sam2014-04-18 03:33
The code is failing at sh.Copy and I cannot figure out why. Any Ideas?
Reply | Reply with quote | Quote
0#Chris2014-05-19 14:08
this code is perfect for what i need,
but could you show code for the exact same process for one sheet rather than the entire workbook?
Reply | Reply with quote | Quote

Add comment


Security code
Refresh