How to Separate Sheets in Excel (6 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

6 Effective Ways to Separate 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.

6 Effective Ways to Separate Sheets in Excel

  • Next, go to the File tab from the ribbon.

  • After that, click on the New option and select Blank Workbook.

6 Effective Ways to Separate Sheets in Excel

  • 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.

6 Effective Ways to Separate Sheets in Excel

  • 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.

6 Effective Ways to Separate Sheets in Excel

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.

6 Effective Ways to Separate Sheets in Excel


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.

6 Effective Ways to Separate Sheets in Excel

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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo