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

Get FREE Advanced Excel Exercises with Solutions!

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.

Download Workbook


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.

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

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.

split a workbook to separate Excel files with VBA code

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

splitting into different files based on column

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

splitting into different files based on column

After that, a Module will be created.

splitting into different files based on column

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.

splitting into different files based on column

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

splitting into different files based on column

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.

splitting into different files based on column

splitting into different files based on column

splitting into different files based on column

split a workbook to separate Excel files with VBA code

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


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.

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with 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

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.
We also defined the pathname to accumulate all of the new files in the separate2 folder.

split all worksheets to separate files

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

split all worksheets to separate files

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.

split all worksheets to separate files

split all worksheets to separate files

split all worksheets to separate files

split all worksheets to separate files

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


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.

split a workbook to separate Excel files with VBA code

split a workbook to separate Excel files with VBA code

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.

split worksheets wit specific names

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

split worksheets wit specific names

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.

split worksheets wit specific names

split worksheets wit specific names

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


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.


Related Articles

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo