How to Export and Save Each Worksheet as Separate New Workbook in Excel

Excel is widely used in various industries and professions, including finance, accounting, data analysis, project management, sales, and more. Its versatility and extensive capabilities make it a powerful tool for manipulating and analyzing data, creating reports and printing them, and making informed business decisions. To export and save each worksheet as a separate new workbook in Excel, you can use VBA (Visual Basic for Applications) code.

How to export and save each worksheet as separate new workbook in Excel

Here's an example of how you can achieve this:

Step 1

Open your Excel workbook.

Create sample data in sheets. In our case, created 3 worksheets as Orders, Products, Customers.

Orders Worksheet

Products Worksheet

Customers Worksheet

Step 2

Press "Alt + F11" to open the Visual Basic Editor.

Insert a new module by clicking on "Insert" > "Module" in the menu.

Paste the following VBA code into the module:

Sub ExportWorksheets()
    Dim OriginalWorkbook As Workbook
    Dim NewWorkbook As Workbook
    Dim OriginalWorksheet As Worksheet
    Dim NewWorksheet As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set OriginalWorkbook = ThisWorkbook 'Assuming the code is running from the 
workbook you want to split
    
    For Each OriginalWorksheet In OriginalWorkbook.Worksheets
        Set NewWorkbook = Workbooks.Add 'Create a new workbook for each worksheet
        Set NewWorksheet = NewWorkbook.Worksheets(1) 'Get the first worksheet of 
the new workbook
        
        OriginalWorksheet.Copy Before:=NewWorksheet 'Copy the original worksheet 
to the new workbook
        
        'Remove other worksheets in the new workbook
        While NewWorkbook.Worksheets.Count > 1
            NewWorkbook.Worksheets(2).Delete
        Wend
        
        'Save the new workbook with the name of the original worksheet
        NewWorkbook.SaveAs "E:\Assignments\3rd Assignments\How to export and save each 
worksheet as separate new workbook in Excel\NewSheets" & OriginalWorksheet.Name & ".xlsx"
        
        NewWorkbook.Close SaveChanges:=False 'Close the new workbook without saving changes
    Next OriginalWorksheet
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True    
    MsgBox "Worksheets exported successfully!", vbInformation
End Sub

The screenshot will be as follows:

Step 3

Modify the line Set OriginalWorkbook = ThisWorkbook if you want to export worksheets from a different workbook. Replace " E:\Assignments\3rd Assignments\How to export and save each worksheet as separate new workbook in Excel\NewSheets" with the desired folder path where you want to save the new workbooks.

Step 4

Press "F5" or click on the "Run" button to execute the code.

Now you get the popup as worksheets exported successfully.

This VBA code will iterate through each worksheet in the original workbook, create a new workbook, copy the current worksheet to the new workbook, delete any additional worksheets in the new workbook, save the new workbook with the name of the original worksheet, and close the new workbook without saving changes. Each worksheet will be saved as a separate new workbook in the specified folder path.

Note

Make sure to replace the file path with your desired destination path before running the code.

In NewSheets folder each worksheet exported and saved as separate new workbook. See screenshot:

Conclusion

Exporting and saving each worksheet as a separate new workbook in Excel can be achieved using VBA (Visual Basic for Applications) code. By following a few simple steps, you can automate this process and efficiently manage your worksheets.

First, open your Excel workbook and access the Visual Basic Editor by pressing "Alt + F11". Insert a new module and paste the provided VBA code into it. The code initializes variables and loops through each worksheet in the workbook. For each worksheet, a new workbook is created, and the original worksheet is copied into it. Additional worksheets in the new workbook are removed to ensure only the copied worksheet remains. The new workbook is then saved with a name derived from the original worksheet's name, and finally closed without saving changes.

By modifying the code as needed and specifying the desired destination folder, you can effectively export each worksheet as an individual workbook. This automation significantly streamlines the process, saving time and effort when working with multiple worksheets in Excel.