Split data into separate workbooks

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.

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

Employee wise Data
Employee wise Data

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.

Settings Sheet
Settings Sheet


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

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


MsgBox "Done"

End Sub

Click here to download practice file.


My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.

Leave a Reply