Sabrina Ayon
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 acquire and see data when comparable or related information is 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.
Download Practice Workbook
You can download the workbook and practice with them.
6 Effective Ways to Separate Sheets in Excel
A worksheet is 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 a different worksheet in a workbook. We have all the employee details of a company in the various worksheets. And 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. Use Copy & Paste to Separate Sheets in Excel
Copy and paste are useful because it enables 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 in excel. To use the Copy & Paste feature in excel, we need to follow some steps.
STEPS:
- Firstly, select the whole data by clicking on the tiny triangle icon in the upper left corner.
- Secondly, to copy the sheet 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.
- Further, to paste the data from the previous workbook, press CTRL + V.
- Now, name the sheet as per your preference.
- Finally, the sheet is now separate from the other sheets.
- You can do this for each sheet and separate all the sheets.
Read More: How to Open Two Excel Files Separately (5 Easy Methods)
2. Apply 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 sheet command. We can also separate the worksheet using this command. Let’s follow the procedure for this.
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.
- Now, from the To book drop-down menu, select a new book and checkmark 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 Split Excel Sheet into Multiple Files (3 Quick Methods)
3. 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:
- To begin with, first, go to the View tab on the ribbon.
- After that, click on Split, under the Window group.
- And 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.
Read More: How to Enable Side-by-Side View with Vertical Alignments in Excel
Similar Readings
- How to Split Screen in Excel (3 Ways)
- [Fix:] Excel View Side by Side Not Working
- How to View Excel Sheets in Separate Windows (4 Methods)
4. New Window Feature to Separate Sheets
We can separate multiple worksheets into different workbooks using the excel New Window toolbar. Let’s see the procedures for separate sheets using the excel toolbar.
STEPS:
- In the beginning, open the workbook that contains many worksheets which you want to separate.
- Secondly, go to the View tab from the ribbon.
- Thirdly, click on New Window, under the Window category.
- Lastly, just by following those steps, you can view that the worksheets are now separated.
Read More: How to Open Multiple Excel Files in One Workbook (4 Easy Ways)
5. Excel VBA to Separate 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 wanted. 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.
Let’s follow the steps below to do this.
STEPS:
- Firstly, go to the Developer tab from the ribbon.
- Secondly, 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.
- Thirdly, 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.
VBA Code:
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 RubSub button or pressing the keyboard shortcut F5.
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.
Read More: How to Split Sheets into Separate Workbooks in Excel (4 Methods)
6. Split 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:
- First, go to the Developer tab from the ribbon.
- Second, 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.
- Next, go to Insert and select Module from the drop-down menu.
- And, this will open up the visual basic window.
- After that, copy and paste the VBA code into your Visual Basic Editor.
VBA Code:
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- Further, press the F5 key or click on the Run Sub 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: How to Split a Workbook to Separate Excel Files with VBA Code
Conclusion
The above methods will assist you in 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 in the ExcelDemy.com blog!
