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.
- How to break VBA password in Excel
- How to send email from Excel spreadsheet with VBA script
- How to save Excel file contains VBA Macros code
- 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.
Step 2:
Press Alt + F11 to open VBA in Excel, or click the Developer tab and click Visual Basic.
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.
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
Step 4:
Click the Run icon on the toolbar to run or press F5 on the keyboard to run the code.
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.
When opening the sheet file if there is a notification like that, click Yes to continue to open the content.
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!
You should read it
- How to create a password protection for Excel sheets
- How to copy sheet from Excel file to another Excel file
- How to Remove a Macro in Excel
- How to hide the Sheet Tabs (sheet bar) in Excel
- Show Sheet Tab in Excel when hidden
- Recover deleted Sheet in Excel
- How to lock Excel Sheet, set a password for the sheet in Excel
- How to Unprotect an Excel Sheet
May be interested
- How to open CSV file when Microsoft Excel is not installedfor those cases where you don't have microsoft excel installed and want to read and edit the csv file, we can open the file via the online google sheets application.
- How to Export Airtable to Excel and Google Sheetsairtable lets you create and share relational databases easily. export your airtable data to google sheets or excel to enjoy the benefits of advanced charts and data visualization.
- How to separate signature background - Separate signature from photo simply and quicklyseparating signature background helps you insert signatures into images, word files, excel files, and design files quickly and simply. this article shows you how to separate text from detailed images.
- Separate text from strings in Excel, for example, and how to do itseparate text from strings in excel, for example, and how to do it. in excel, there are 3 basic functions that help you separate text from strings in excel: left, right, mid. if you are looking to separate words from a string, please refer to the article
- How to print multiple sheets at once in Excel spreadsheetsafter you finish drafting an excel document of two or more sheets and want to print them on paper, you have trouble knowing how to print multiple sheets at once. tipsmake.com has written this tutorial to show you how to print multiple sheets at once in excel spreadsheets.
- MS Excel - Lesson 4: Working with lines, columns, sheetsin addition to the content of tables in excel, operations with lines, columns and sheets in this program are also important. in this article, tipsmake.com will guide you to simple operation to add or remove lines, columns or sheets in excel tables.
- How to copy and move Sheets in Excel simply and easilycopying and moving sheets in excel is an important operation to help manage data effectively. this article will guide you through quick, simple ways to do it, suitable for many versions of excel.
- Google Sheets automatically creates tables with just 1 click, making Excel converters excitedgoogle has finally added automatic one-click table creation in sheets, something excel has had for years.
- Applications that read Excel files on iPhone / iPad qualityfor more convenient workflow, we can directly create excel file right on ios devices. therefore, the use of applications with the feature to read excel files on iphone / ipad is essential.
- How to separate first and last name in excelhow to separate names in excel? this may seem simple but not everyone knows?