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

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

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

splitting into different files based on column

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

splitting into different files based on column

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

We declared sht as Worksheet, region_book as Workbook, region_info, region_value as Range, and the region as String. We set sht to the sheet January and region_info to the range B3:D10 of this worksheet.
We have to use 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, a FOR loop was used to create files for each region name in the range C15:C100. The region was assigned to the region strings of this range, and the region_book was set to store all of the newly created Excel files.
We declared the path of the folder separate1 to store all of the files and finally, we 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.
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 find the sales records for each region in that region’s file, as shown in the following figures.

splitting into different files based on column

split a workbook to separate Excel files with VBA code

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

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

We declared records as Worksheet and then used the FOR loop to copy each workbook sheet and paste it into the files with the name after the worksheets.
We also defined the pathname to accumulate all new files in the separate2 folder.

split all worksheets to separate files

➤ Press F5.
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

Method 3 – Split Worksheets of a Workbook with Specific Names to Separate Excel 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

We declared records as Worksheet and then used the FOR loop to copy each workbook sheet 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 defined the pathname to accumulate all new files in the separate3 folder.

split worksheets wit specific names

➤ Press F5.
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 find the sales records from the January and January (2) sheets of the old workbook in the new files after the names of those sheets, as shown in the following figures.

split worksheets wit specific names