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:

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 good idea3


good idea4Split Workbook

Kutools for Excel's Split Workbok help you split all sheets from a workbook and save them as separate workbooks, texts, csv files or pdf files.
doc split workbook

arrow blue right bubble Export and save worksheets as new workbook 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.
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

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, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

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

Step 2: In the Split Workbook dialog box, do as below:

doc split workbook 1
A: 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;


B: If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets.);


C: and also you can check Specify save format, and to select split the sheets then save as text file, pdf file, or csv file, in defualt, if you do not check this option, it will split sheets and save as workbook.

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.

arrow blue right bubble Export and save each sheet as new workbook


goodExport Range to File

Kutools for Excel's Export Range to File function can export or save a range to separate file as
workbook, pdf, text, csv or text.
Click Enterprise > Import/Export > Export Range to File.
doc export cell range to file

Related articles:

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +2 Nasser
thanks alot, works very well and easy.
2013-12-07 10:49 Reply Reply with quote Quote
Permalink 0 Sean
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
2014-01-26 12:06 Reply Reply with quote Quote
Permalink 0 Rajesh Jayagopi
Thanks a lot. Good Job!.
2014-02-13 11:05 Reply Reply with quote Quote
Permalink +1 Keith
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
2014-03-18 20:49 Reply Reply with quote Quote
Permalink 0 Phani
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.
2014-04-07 15:20 Reply Reply with quote Quote
Permalink +1 Votefor Pedro
Thank you. This is esactly what I needed.
2014-04-17 20:24 Reply Reply with quote Quote
Permalink 0 Sam
The code is failing at sh.Copy and I cannot figure out why. Any Ideas?
2014-04-18 03:33 Reply Reply with quote Quote
Permalink 0 Chris
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?
2014-05-19 14:08 Reply Reply with quote Quote
Permalink 0 farfan
The VBA codes works like magic, but is there a way for this to be done using VBScript? I'm going nuts trying to meet the requirement from my supervisor.
2014-08-01 01:30 Reply Reply with quote Quote
Permalink 0 fauzan
thank. terimakasih. ini code yang saya butuhkan
2015-01-11 08:54 Reply Reply with quote Quote
Permalink 0 AC
Very Useful thanks! Loved the VBA Code
2015-09-18 21:43 Reply Reply with quote Quote
Permalink 0 JoaoM
Muy buena la aplicacion.

Es una pena no existir tambien en Español.

Gracias
2015-09-29 14:22 Reply Reply with quote Quote
Permalink 0 Charlie Clarke
Unfortunately the VBA code didn't work for me. I had to debug and the issue is with the line:
Application.Act iveWorkbook.Sav eAs xFile, FileFormat:=FileFormatNum

Any ideas why it didn't work for me?
2015-10-02 14:21 Reply Reply with quote Quote
Permalink 0 Louise
Great article - VBA worked first time - perfect
2015-10-23 15:50 Reply Reply with quote Quote
Permalink +2 Beth G
xWs.Copy -it is an error in my part what does it mean?
2016-03-09 06:53 Reply Reply with quote Quote
Permalink 0 Beth G
Run time error Method 'Copy of Object' worksheet' failed
2016-03-09 06:56 Reply Reply with quote Quote
Permalink 0 Madhav Mehendale
Out of the above three methods, I found the first method to be quite good having regard to the fact that I am a beginner & my work is not at all of the same magnitude like professionals. My sincere thanks for explaining the steps in simple and lucid manner.
2016-03-25 14:15 Reply Reply with quote Quote
Permalink 0 Richard
Can we also export ALL (300+) sheets to html,
this will help me a lot, the export to unified text is already helping but I need them in html, or no table borders for the cell differences
2016-10-21 12:50 Reply Reply with quote Quote
Permalink 0 DAS
Your code worked for me... Thanks much!!!
2017-01-19 18:28 Reply Reply with quote Quote
Permalink 0 Rae
Hi. I received an error code that says "Compile error: Invalid outside procedure"...he lp!
2017-02-01 21:04 Reply Reply with quote Quote
Permalink 0 Aung
This is exactly what I needed! Awesome!
2017-06-19 08:22 Reply Reply with quote Quote

Add comment


Security code
Refresh