While working with Microsoft Excel, we may present additional worksheets to reorganize our workbook and make it clearer to discover material while dealing with a massive volume of data. It is able to recognize comparable or related information organized in various spreadsheets. Sometimes, we need to separate those sheets into different workbooks to deal with clients’ requirements. This article will demonstrate some effective ways to separate sheets in Excel.
A worksheet also known as a spreadsheet, is a file with rows and columns that may be used to quickly sort, organize, and arrange data as well as estimate quantitative data. We can have our data in that spreadsheet/worksheet to store them safely.
Suppose, we have different worksheets in a workbook. We have all the employee details of the company in the various worksheets. The company contains six departments; General Management Department, Marketing Department, Operations Department, Sales Department, Finance Department, and IT Department. Now, we want to keep all that information in different workbooks. For this, we need to separate all those worksheets. Let’s see the ways to separate those sheets in Excel.
1. Using Copy and Paste to Separate Sheets in Excel
Copy and paste are useful because they enable you to move material from one place to the other without having to retype it each time. The capability to use all data again in extensive content is provided through the copy and paste tool likewise shown in the screenshot below. To use the Copy & Paste feature in Excel, we need to follow some steps.
STEPS:
- Select the whole data by clicking on the tiny triangle icon in the upper left corner.
- Copy the sheet and press CTRL + C.
- Next, go to the File tab from the ribbon.
- After that, click on the New option and select Blank Workbook.
- This will open a new workbook. Now, similar to the previous step, again click on the small icon in the upper left corner of the spreadsheet.
- Paste the data from the previous workbook, and press CTRL + V.
- Rename the sheet as per your preference.
- The sheet is now separate from the other sheets.
You can do this for each sheet and separate all the sheets.
Read More: Splitting Text in Excel
2. Applying Move or Copy Feature to Split Sheets
The Move or Copy dialog box lets you copy a sheet to a new position in the existing document or to another document. To move or copy complete worksheets, use the Move or Copy command. We can also separate the worksheet using this command.
STEPS:
- In the first place, select the sheet that you want to separate.
- Then, right-click on the mouse and select Move or Copy.
- This will open the Move or Copy dialog box.
- From the To book drop-down menu, select a new book and check the Create a copy box.
- Then, click OK.
And, that’s it. This will separate the worksheet and you can separate all the worksheets by following the above steps.
Read More: How to Separate Text and Numbers in Excel
3. Using Excel Split Tool to Separate Sheet into Panes
When using Excel, splitting screens vertically or horizontally is a very useful method to optimize data productivity. This feature allows us to compare data quickly by allowing us to examine distinct areas of a spreadsheet at the same time. We can use the Split tool in Excel to separate sheets into panes. Let’s see the procedures to use this tool for separating sheets.
STEPS:
- First, go to the View tab on the ribbon.
- Click on Split, under the Window group.
- This will separate the worksheets into panes in one worksheet.
If you don’t want to use the feature, just click again on the Split tool.
4. Using New Window Feature for Sheet Separations
We can separate multiple worksheets into different workbooks using the New Window feature. Let’s see the procedures for separate sheets using the Excel toolbar.
STEPS:
- In the beginning, open the workbook that contains many worksheets that you want to separate.
- Go to the View tab from the ribbon.
- Click on New Window, under the Window group.
- By following those steps, you can see that the worksheets are now separated.
5. Running VBA Macro to Separate Excel Sheets
Excel VBA is the most important feature, with this we can manipulate our work as per the requirements. With Excel VBA, we can specifically separate our worksheets as we want. It would be the best way to separate sheets.
For example, suppose we have our Excel file in that particular path on our computer. We want to separate the workbook sheets on the same path.
STEPS:
- Go to the Developer tab from the ribbon.
- From the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- This will appear in the Visual Basic Editor.
- Click on Module from the Insert drop-down menu bar.
- This will create a Module in your workbook.
- And, copy and paste the VBA code shown below.
Sub SeparateSheet()
Dim fileLoc As String
fileLoc = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WSheet In ThisWorkbook.Sheets
WSheet.Copy
Application.ActiveWorkbook.SaveAs Filename:=fileLoc & "\" & WSheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- After that, run the code by clicking on the Run button or pressing the F5 key.
You don’t need to change or edit the VBA code. All you need to do is just copy and paste the code.
- This will separate all the sheets in the same path on your computer.
6. Splitting Excel Sheets into PDFs
If you want to separate the sheets as PDF files. You can do this using the following VBA code. Let’s look at the steps to do that.
STEPS:
- Go to the Developer tab from the ribbon.
- Click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, right-click on the sheet, then select View Code.
- Go to Insert and select Module from the drop-down menu.
- This will open up the visual basic window.
- Copy and paste the VBA code into your Visual Basic Editor.
Sub SeparateWorksheet()
Dim fileLoc As String
fileLoc = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WSheet In ThisWorkbook.Sheets
WSheet.Copy
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileLoc & "\" & WSheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub- Press the F5 key or click on the Run button to run the code.
Don’t change the code. Just copy the code and paste it into your Visual Basic Editor module. Finally, you can see that the sheets are now separated as PDFs in the same location on your computer.
Read More: Split Names in Excel
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you to separate sheets in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles on our website!
