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.

Table of Contents hide

Download Practice Workbook

4 Methods to Split Sheets into Separate Workbooks in Excel

1. Creating Separate Workbooks Manually

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

3. Splitting Sheets into an Existing Workbook

4. Using VBA Code to Split Sheets into Separate Workbooks

Conclusion

Download Practice Workbook

Split Sheets into Separate Workbooks.xlsm

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.

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

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

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

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

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.

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

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

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.

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

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

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.

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

➜ Secondly, go to Insert > Module.

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

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

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!

2 Comments

p.kuipers Aug 2, 2022 at 3:40 PM

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.
- Reply
  Eshrak Kader Aug 14, 2022 at 11:21 AM
  
  Hello P.KUIPERS,
  Thank you for your question. The ExcelDemy team has created an Excel file with the solution to your question that you may download from the link below.
  Rename_Sheets.xlsm
  
  You can download the practice files from the link below
  Rename_Sheets_Do_Yourself.xlsm
  
  Otherwise, you can just follow the steps below.
  
  In order to rename the sheets in a sequential way, you can use another VBA Macro. So, let’s see the step below.
  
  Step: 1
  a. Firstly, navigate to the Developer tab >> click the Visual Basic button. This opens the Visual Basic Editor in a new window.
  b. Next, go to the Insert tab >> select Module.
  For your ease of reference, you can copy the code from here and paste it into the window.
  
  Sub Rename_Multiple_Sheets()
  
  Alphabets = Array(“A”, “B”, “C”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”)
  
  Days = Array(“Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)
  
  Dim Weekdays(5) As String
  
  For i = 0 To 4
  Weekdays(i) = Days(i)
  Next i
  
  Months = Array(“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)
  
  Old_Names = InputBox(“Enter the Names of the Worksheets to Change (Separate them be Commas).” + vbNewLine + “OR” + vbNewLine + “Enter ALL to change all the worksheets.”)
  
  If Old_Names = “ALL” Then
  Dim Old_Sheets() As String
  ReDim Old_Sheets(Sheets.Count – 1)
  For i = 0 To Sheets.Count – 1
  Old_Sheets(i) = Sheets(i + 1).Name
  Next i
  Else
  Old_Sheets = Split(Old_Names, “,”)
  End If
  
  Dim Used_Names() As String
  
  ReDim Used_Names(0)
  
  Dim Sign As Integer
  
  Sequential_Or_Random = Int(InputBox(“Enter 1 to Change the Worksheet Names in a Sequential Way: ” + vbNewLine + “OR” + vbNewLine + “Enter 2 to Change the Worksheet Names in a Random Way: “))
  
  If Sequential_Or_Random = 1 Then
  
  Series = Int(InputBox(“Enter 1 to Change the Names to a Series of Numbers: ” + vbNewLine + “Enter 2 to Change the Names to a Series of ALphabets: ” + vbNewLine + “Enter 3 to Change the Names to a Series of Days: ” + vbNewLine + “Enter 4 to Change the Names to a Series of Weekdays: ” + vbNewLine + “Enter 5 to Change the Names to a Series of Months: “))
  
  If Series = 1 Then
  Prefix = InputBox(“Enter the Prefix before the Numbers: “)
  First_Number = Int(InputBox(“Enter the First Number: “))
  Increment = Int(InputBox(“Enter the Increment: “))
  For i = 0 To UBound(Old_Sheets)
  Sheets(Old_Sheets(i)).Name = Prefix + Str(First_Number + Increment * (i))
  Next i
  
  ElseIf Series = 2 Then
  Prefix = InputBox(“Enter the Prefix before the Letters: “)
  First_Letter = InputBox(“Enter the First Letter: : “)
  Increment = Int(InputBox(“Enter the Increment: “))
  Dim Case_Identifier As String
  For i = 0 To UBound(Alphabets)
  If Alphabets(i) = First_Letter Then
  First_Letter_Number = i
  Case_Identifier = “U”
  Exit For
  ElseIf LCase(Alphabets(i)) = First_Letter Then
  First_Letter_Number = i
  Case_Identifier = “L”
  Exit For
  End If
  Next i
  For i = 0 To UBound(Old_Sheets)
  Sign = 0
  For j = 0 To UBound(Used_Names)
  If Alphabets((First_Letter_Number + (Increment * i)) Mod 26) = Used_Names(j) Then
  Sign = Sign + 1
  End If
  Next j
  If Sign = 0 Then
  If Case_Identifier = “U” Then
  Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26)
  Else
  Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26))
  End If
  Else
  If Case_Identifier = “U” Then
  Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26) + ” (” + Str(Sign) + “)”
  Else
  Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26)) + ” (” + Str(Sign) + “)”
  End If
  End If
  ReDim Preserve Used_Names(i)
  Used_Names(i) = Alphabets((First_Letter_Number + (Increment * i)) Mod 26)
  Next i
  
  ElseIf Series = 3 Then
  First_Day = LCase(InputBox(“Enter the First Day: : “))
  Increment = Int(InputBox(“Enter the Increment: “))
  For i = 0 To UBound(Days)
  If LCase(Days(i)) = First_Day Then
  First_Day_Number = i
  Exit For
  End If
  Next i
  For i = 0 To UBound(Old_Sheets)
  Sign = 0
  For j = 0 To UBound(Used_Names)
  If Days((First_Day_Number + (Increment * i)) Mod 7) = Used_Names(j) Then
  Sign = Sign + 1
  End If
  Next j
  If Sign = 0 Then
  Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7)
  Else
  Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7) + ” (” + Str(Sign) + “)”
  End If
  ReDim Preserve Used_Names(i)
  Used_Names(i) = Days((First_Day_Number + (Increment * i)) Mod 7)
  Next i
  
  ElseIf Series = 4 Then
  First_Weekday = LCase(InputBox(“Enter the First Day: : “))
  Increment = Int(InputBox(“Enter the Increment: “))
  For i = 0 To UBound(Weekdays)
  If LCase(Weekdays(i)) = First_Weekday Then
  First_Weekday_Number = i
  Exit For
  End If
  Next i
  For i = 0 To UBound(Old_Sheets)
  Sign = 0
  For j = 0 To UBound(Used_Names)
  If Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) = Used_Names(j) Then
  Sign = Sign + 1
  End If
  Next j
  If Sign = 0 Then
  Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)
  Else
  Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) + ” (” + Str(Sign) + “)”
  End If
  ReDim Preserve Used_Names(i)
  Used_Names(i) = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)
  Next i
  
  ElseIf Series = 5 Then
  First_Month = LCase(InputBox(“Enter the First Month: “))
  Increment = Int(InputBox(“Enter the Increment: “))
  For i = 0 To UBound(Months)
  If LCase(Months(i)) = First_Month Then
  First_Month_Number = i
  Exit For
  End If
  Next i
  For i = 0 To UBound(Old_Sheets)
  Sign = 0
  For j = 0 To UBound(Used_Names)
  If Months((First_Month_Number + (Increment * i)) Mod 12) = Used_Names(j) Then
  Sign = Sign + 1
  End If
  Next j
  If Sign = 0 Then
  Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12)
  Else
  Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12) + ” (” + Str(Sign) + “)”
  End If
  ReDim Preserve Used_Names(i)
  Used_Names(i) = Months((First_Month_Number + (Increment * i)) Mod 12)
  Next i
  End If
  
  ElseIf Sequential_Or_Random = 2 Then
  New_Names = InputBox(“Enter the New Names (Separate them by Commas): “)
  New_Sheets = Split(New_Names, “,”)
  For i = 0 To UBound(Old_Sheets)
  Sign = 0
  For j = 0 To UBound(Used_Names)
  If New_Sheets(i) = Used_Names(j) Then
  Sign = Sign + 1
  End If
  Next j
  If Sign = 0 Then
  Sheets(Old_Sheets(i)).Name = New_Sheets(i)
  Used_Names(Count) = New_Sheets(i)
  Count = Count + 1
  Else
  Sheets(Old_Sheets(i)).Name = New_Sheets(i) + ” (” + Str(Sign) + “)”
  End If
  ReDim Preserve Used_Names(i + 1)
  Used_Names(i + 1) = New_Sheets(i)
  Next i
  End If
  
  End Sub
  
  Step: 2
  a. Secondly, close the Visual Basic Editor >> in the top Ribbon, click the Macros button >> Now, select the Rename_Multiple_Sheets Macro and press Run.
  b. This opens up a few input boxes. The inputs are described in the step below therefore just follow these steps.
  
  Step: 3
  a. The first Input Box will ask you to enter the name of the sheets that you want to change. Since you want to rename your worksheets to Sheet1, Sheet2, etc. you can type in ALL.
  b. The second Input Box will ask you whether you change the sheet names in a sequential way or in a random way. In this case, you enter 1.
  c. If you go for a sequential way, the third Input Box will ask for a series of values from the options below. Now, enter 1 for a series of Numbers (1, 2, 3, etc.)
  d. Next, enter a Prefix before the numbers, for instance, “Sheet”.
  e. Then, give the Starting Number, in this case, 1.
  f. Lastly, provide the Increment for the numbers, for example, you can choose 1.
  Voila! All your sheets are numbered serially as Sheet 1, Sheet 2, etc.
  If you wish you can learn more about this VBA Code in this article.

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

Download Practice Workbook

4 Methods to Split Sheets into Separate Workbooks in Excel

1. Creating Separate Workbooks Manually

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

3. Splitting Sheets into an Existing Workbook

4. Using VBA Code to Split Sheets into Separate Workbooks

Conclusion

Related Articles

Md. Abdul Kader

How to Randomize List of Names in Excel (8 Easy Methods)

How to Make Exponent in Excel Text (7 Easy Methods)

How to Use Excel Exponential Function of Base 10 (6 Examples)

How to Use Negative Exponents in Excel (3 Easy Methods)

Leave a reply Cancel reply