VBA: Split Data into Separate Workbooks

In this article you will learn how we can split our data into multiple workbooks. Every workbook will be saved in the given folder path. This macro can be used when you have a long list of your data and you have to make different files with specific data. This is ready to use template for Split Data into Separate Workbooks.

Split Data into Separate Workbooks

Here in this example we have taken Employee wise performance data and we have to split our data for each supervisor.

We have created a “Settings” sheet also. In this sheet we have the Folder path wherein our files will be saved. We also have created a button to run the macro.

Below is the macro code.

• Copy the below given code and go to Visual Basic Editor (Press Alt+F11)
• Insert a module (Press Alt+I+M)
• Paste this code in the module.
• Save as the workbook as Macro enable workbook.

Option Explicit

Sub Split_Data_in_workbooks()

Application.ScreenUpdating = False

Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Data")

Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")

Dim nwb As Workbook
Dim nsh As Worksheet

''''' Get unique supervisors

setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("B:B").Copy setting_Sh.Range("A1")

setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

Dim i As Integer

For i = 2 To Application.CountA(setting_Sh.Range("A:A"))

data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)

data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
nsh.UsedRange.EntireColumn.ColumnWidth = 15

nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
nwb.Close False
data_sh.AutoFilterMode = False
Next i

setting_Sh.Range("A:A").Clear

MsgBox "Done"

End Sub

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step-by-step video tutorial: