How to split Excel sheets into multiple files.
By
SpreadCheaters
Managing large datasets in Excel can be challenging, particularly when you need to share specific portions of the data with others. Splitting the Excel sheet into Multiple Files based on a certain criterion can help streamline data distribution and improve collaboration.
Here we have two datasets, in these datasets, we have different kinds of information in two different Sheets which we need to save separately. In this tutorial, we will explore efficient methods to split an Excel sheet into multiple files, but first, let’s take a look at the Dataset.
Method – 1 Using Copying and Pasting.
If you need to split only a few worksheets as separate files, this is an easy-to-use way. Manually, you can save a workbook as a separate Excel file using the Copy command and Paste command method.
Step – 1 Select the sheet.
- Select the Sheet that you want to create a separate file of.
- Create a new Workbook.
Step – 2 Copy the sheet.
- Select the whole Sheet by clicking on the arrow at the top left corner.
- Then go to the Home tab.
- In the Clipboard group, click the Cut command or press Ctrl + X.
Step – 3 Paste the sheet
- After copying the sheet go to the new Workbook.
- In the Clipboard group click on the Paste option.
- The sheet be pasted in the new Workbook.
- You should repeate the same process as many as times as many sheets are there in the original workbook. Finally the original workbook’s data will be split into many files.
Method – 2 Using VBA code.
Step – 1 Open Microsoft Basic.
- Because the split Excel files will stay in the same folder as this master workbook, create a new folder specifically for the workbook that you want to split.
- Hold down the ALT + F11 keys in Excel, this will open the Microsoft Visual Basic for Applications window.
Step – 2 Split the sheet.
- Click Insert > Module, and paste the following code in the Module Window.
| 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 & “.xlsx” Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
- Press the F5 key to run this code. The workbook will be split into separate Excel files in the same folder as the original workbook.
Conclusion:
Splitting an Excel sheet into multiple files can significantly enhance data management and collaboration. Whether you’re sharing specific sections of data or organizing information by category, these methods allow for efficient file distribution. By following the outlined steps, you can streamline your data-sharing process and enhance productivity in Excel.
