How to separate sheets into separate Excel files

On an Excel file you can create multiple work sheets so that the content between sheets is more seamless, viewers can convert content, track each other's content related to each sheet more easily. This also limits the need to open multiple Excel files at the same time to track the data. However, if an Excel file has multiple sheets, it will increase the size of Excel files, especially if you use additional add-ins or VBA macro code in Excel.

If the user wants to separate each sheet into individual Excel files, it is also very simple. The contents of the sheet remain the same, the sheet name becomes the name of each file and the formula used in the sheet is not changed or the result error. The following article will guide you how to separate each sheet in Excel into separate files.

1. How to break VBA password in Excel
2. How to send email from Excel spreadsheet with VBA script
3. How to save Excel file contains VBA Macros code
4. How to create flashing letters on Excel

Video tutorial for separating sheets in Excel

Instructions for separating sheets in Excel separately

Step 1:

First of all, Excel must show the Developer tab first. Then open the Excel file to split the sheets. Below will have 3 sheets with different sheet names.

How to separate sheets into separate Excel files Picture 1

Step 2:

Press Alt + F11 to open VBA in Excel, or click the Developer tab and click Visual Basic.

How to separate sheets into separate Excel files Picture 2

Step 3:

Switch to the content interface new users click on the Insert tab and then click on the Module section to open the VBA code entry interface.

How to separate sheets into separate Excel files Picture 3

Next, copy the code below into the interface.

 Sub Splitbook () 
 'Updateby20140612 
 Dim xPath As String 
 xPath = Application.ActiveWorkbook.Path 
 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False 
 For Each xWs In ThisWorkbook.Sheets 
 xWs.Copy 
 Application.ActiveWorkbook.SaveAs Filename: = xPath & "" & xWs.Name & ".xls" 
 Application.ActiveWorkbook.Close False 
 next 
 Application.DisplayAlerts = True 
 Application.ScreenUpdating = True 
 End Sub 

How to separate sheets into separate Excel files Picture 4

Step 4:

Click the Run icon on the toolbar to run or press F5 on the keyboard to run the code.

How to separate sheets into separate Excel files Picture 5

When the code process is completed, open the original Excel file store and see each sheet file displayed here. Each sheet name is converted to each Excel file name so users can easily manage files. All formulas used in each sheet remain the same.

How to separate sheets into separate Excel files Picture 6

When opening the sheet file if there is a notification like that, click Yes to continue to open the content.

How to separate sheets into separate Excel files Picture 7

With just a simple operation, you get the sheet files in Excel quickly. All content on each sheet of Excel has not been changed.

I wish you all success!