How to Split a Workbook to Separate Excel Files with VBA Code

If you are looking for some of the easiest ways to split a workbook to separate Excel Files with VBA code, then you are in the right place. Using the VBA codes of this article you can split your workbook based on different criteria quickly. So, let’s start with our main article.

Table of Contents hide

Download Workbook

3 Ways to Split a Workbook to Separate Excel Files with VBA Code

Method-1: Split a Worksheet of a Workbook Based on a Column to Separate Excel Files

Method-2: Split All Worksheets of a Workbook to Separate Excel Files

Method-3: Split Worksheets of a Workbook with Specific Names to Separate Excel Files

Conclusion

Download Workbook

Split Workbook.xlsm

3 Ways to Split a Workbook to Separate Excel Files with VBA Code

Here, we have the following workbook Split Workbook.xlsm with 4 different sheets; January, January (2), February, and March. Using the following 3 different procedures we will try to split this workbook into separate excel files based on our needs.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Split a Worksheet of a Workbook Based on a Column to Separate Excel Files

Suppose, we have a single worksheet January in the workbook Split Workbook.xlsm and we want to divide this workbook into different Excel files based on different regions of the Region column. To do this you can follow this method.

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

Step-02:
➤ Write the following code

Sub Separate_book_to_sheets1()
Dim sht As Worksheet
Dim region_book  As Workbook
Dim region_info, region_value  As Range
Dim region  As String
Set sht = ActiveWorkbook.Sheets("January")
Set region_info = sht.Range("B3:D10")
sht.Range("C4:C10").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=sht.Range("C15:C100"), Unique:=True
For Each region_value In sht.Range("C15:C100")
region = region_value.Text
Set region_book = Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\Users\Mima\Downloads\separate1\" _
& region & ".xlsx"
Application.DisplayAlerts = False
sht.Activate
sht.AutoFilterMode = False
region_info.AutoFilter field:=2, Criteria1:=region
region_info.Copy
region_book.Activate
ActiveSheet.Paste
ActiveWorkbook.Close SaveChanges:=True
Next region_value
End Sub

Here, we have declared sht as Worksheet, region_book as Workbook, region_info, region_value as Range, and the region as String. Then, we set sht to the sheet January and region_info to the range B3:D10 of this worksheet.
Then, we have to use the Advanced Filter method in the Region column to extract the unique values from this column to the range C15:C100 based on which we will create the new files with the data of that particular region.
Later FOR loop has been used to create files for each region name in the range C15:C100 and then the region is assigned to the region strings of this range and the region_book is set to store all of the newly created excel files.
We have declared the path of the folder separate1 to store all of the files and finally, we have used the AutoFilter method to copy the filtered data based on the region and then paste it to that corresponding region file.

➤ Press F5.
After that, the newly created files named after the unique regions will be amassed inside the Separate1 folder.

If you open the files one by one, you will have the sales records for each region in that region’s file like the following figures.

Method-2: Split All Worksheets of a Workbook to Separate Excel Files

Here, we will split all of the sheets of the Split Workbook.xlsm file into four different excel files easily with the help of a VBA code.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Separate_book_to_sheets2()
Dim records As Worksheet
Application.ScreenUpdating = False
For Each records In ActiveWorkbook.Sheets
records.Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\Mima\Downloads\separate2\" _
& records.Name & ".xlsx"
Next
End Sub

➤ Press F5.
Then, we will have the newly created files with the names after the sheet names inside the Separate2 folder.

After opening the files, you will have the sales records for each month in the new files after the names of the previous sheets like the following figures.

Similar Readings

Method-3: Split Worksheets of a Workbook with Specific Names to Separate Excel Files

In this section, we will divide the workbook Split Workbook.xlsm into separate worksheets based on the sheet names with January only. That means we will only create new files for the worksheets January and January (2) among all of the 4 files.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Separate_book_to_sheets3()
Dim records As Worksheet
Application.ScreenUpdating = False
For Each records In ThisWorkbook.Sheets
If InStr(records.Name, "January") > 0 Then
records.Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\Mima\Downloads\separate3\" _
& records.Name & ".xlsx"
End If
Next
End Sub

Here, we have declared records as Worksheet and then used the FOR loop to copy each sheet of the workbook and paste it into the files with the name after the worksheets.
IF statement with the InStr function is used here to find the sheets starting with January name only among all the other sheets.
We also defined the pathname to accumulate all of the new files in the separate3 folder.

➤ Press F5.
Eventually, you will have the new files with the names after the sheet names inside the Separate3 folder.

After opening the files, you will have the sales records from the January and January (2) sheets of the old workbook in the new files after the names of those sheets like the following figures.

Conclusion

In this article, we tried to cover the ways to split a workbook to separate Excel files with VBA code easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.