How to Split Sheets into Separate Workbooks in Excel (4 Methods)

When you have a larger dataset covering numerous working sheets, you may need to split those worksheets into dedicated workbooks for analyzing extensively. In this article, I’ll discuss 4 methods including VBA code to split sheets into separate workbooks in Excel with the necessary explanation.


Download Practice Workbook


4 Methods to Split Sheets into Separate Workbooks in Excel

Let’s introduce today’s dataset. Here, Selling Information of Items is provided for 6 months across different sheets. The following screenshot (Sheet name: ‘January’) depicts the information in January. Now, I’m going to show how you can extract the worksheets as workbooks based on your purposes.

Dataset

Let’s explore the methods.


1. Creating Separate Workbooks Manually

If you want to create one or more distinct workbooks and skip any complexity, you may betake this method.

➜ Initially, select the entire dataset and copy (just press CTRL + C).

Creating Workbook Manually

➜ Later, go to File > New or File > Home (Blank workbook) to create a new workbook.

Creating Workbook Manually

➜ Now, paste the copied data (by pressing CTRL + V) and save the file as Excel Workbook (*.xlsx). And rename the workbook as January.

Excel Split Sheets into Separate Workbooks Creating Workbook Manually

Here’s I want to share a simple trick. You may check the name of the workbook using the following formula which is mainly the combination of the MID, CELL, and FIND functions.

=MID(CELL("filename",B5),FIND("[",CELL("filename",B5))+1,FIND("]", CELL("filename",B5))-FIND("[",CELL("filename",B5))-1)

Here, B5 is the starting cell of the Order ID. However, you may insert any cell within the workbook.

In the above screenshot, I used the formula in the G5 cell and found that the workbook is January (created this a while ago).

Read More: How to Separate Sheets in Excel (6 Effective Ways)


2. Right-Clicking over Sheet Tab to Split Sheets into Workbooks

But it’s not fruitful to use the simplest copy-pasting method. Besides, when you need to split some sheets into a specific workbook. For example, you may want to create a separate workbook containing four working sheets (e.g. January, February, March, and April).

➜ Firstly, go to the Sheet tab (located at the lower part of the working sheet). Then, select the specified working sheet holding the CTRL key simultaneously.

➜ After executing that, choose the Move or Copy… from the Context Menu.

Right-clicking over Sheet Tab

➜ Subsequently, pick (new book) under the drop-down list of the To book option and also check the box before the Create a copy option.

Right-clicking over Sheet Tab

After saving the workbook as Workbook2, you’ll get the following output.

Right-clicking over Sheet Tab

Read More: How to Split Excel Sheet into Multiple Files (3 Quick Methods)


Similar Readings


3. Splitting Sheets into an Existing Workbook

Furthermore, you can split any sheet into an existing workbook instead of creating a new workbook. For instance, you want to extract the sheet namely May to the Workbook2 (previously created workbook).

➜ Go to the Sheet tab and choose the Move or Copy…option (as shown in the second method) while keeping the cursor over the May sheet.

Splitting a Worksheet to an Existing Workbook

➜ Later, pick (Workbook2.xlsx) under the drop-down list of the To book option. In addition, choose (move to end) under the Before sheet option. And also check the box before the Create a copy option.

Splitting a Worksheet to an Existing Workbook

Eventually, your copied sheet is added to Workbook2 as shown in the following image.

Splitting a Worksheet to an Existing Workbook

Read More: How to Split Excel Sheet into Multiple Worksheets (3 Methods)


4. Using VBA Code to Split Sheets into Separate Workbooks

How would you feel if you are to able to split every sheet in your workbook into separate workbooks directly? Sounds interesting! Exactly, you can do that.

The following screenshot displays the Split Sheets into Workbooks. In this folder, you need to create separate workbooks.

Excel Split Sheets into Separate Workbooks Using VBA Code

Before doing that you need to create a module to insert the VBA code.

➜ Firstly, open a module by clicking Developer > Visual Basic (or press ALT + F11).

How to Insert VBA Code

➜ Secondly, go to Insert > Module.

How to Insert VBA Code

Then, copy the following code into the newly created module.

Sub SplitShts()
Dim CurrentWb As Workbook
Dim NewWb As Workbook
Dim Sht As Worksheet
Dim Filename As String

    Set CurrentWb = ThisWorkbook
    For Each Sht In CurrentWb.Worksheets
        Filename = CurrentWb.Path & "/" & Sht.Name & ".xlsx"
        Sht.Copy
        Set NewWb = ActiveWorkbook
        NewWb.SaveAs Filename
        NewWb.Close
    Next Sht
End Sub

Excel Split Sheets into Separate Workbooks Using VBA Code

Code Explanation:

  • In the above code, I declared CurrentWb(this workbook), NewWb(new workbook) as Workbook, Sht as Worksheet, and Filename as String type first.
  • Then, I set CurrentWb with the ThisWorkbook property. After that, I ran For…Next statement to split the sheets into separate workbooks.
  • Inside the loop, I also defined the Filename with the Path property (to represent the path of the active workbook), the Sht.Name (to return the sheet name), and .xlsx which is the default extension of any Excel file.
  • Subsequently, I used the Copy method to copy the sheet. Lastly, I set the NewWb and close the statement.

After running the code (keyboard short is F5), you’ll get the following output (separated workbooks) at the path of your previous workbook.

Excel Split Sheets into Separate Workbooks Using VBA Code

However, you may also split worksheets based on the column using VBA.


Conclusion

That’s all for today. I firmly believe that from now you can spit the sheets into separate workbooks in Excel accordingly to your requirement. Anyway, don’t forget to share your thoughts.

Explore Exceldemy!


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

1 Comment
  1. With the VBA, is it possible to extend the code to rename the sheets in the new workbooks to “Sheet1”?
    It would be easier for me to automate loading the data for processing if the sheets do not have unique names.

Leave a reply

ExcelDemy
Logo