Split data to Workbooks using VBA

Hello friends! It’s been very long I’ve written any article. I was super busy since last 4-5 months.

Today we’ll be learning to create to split workbooks based on some column values. Recently I got requirement to split workbooks based on Manager name. Here I’ve countries statistics and will be using to demo it. I’ve attached the workbook to download and reuse it.

Data

As you can see in the image below the details of the countries and different stats about them. Our goal is to create workbooks based on Region(Column B).

VBA Code

Option Explicit
Sub Split_Data_in_workbooks()
Application.ScreenUpdating = False

'Data sheet with the data 
Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("DataSheet")

'User click sheet
Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("UserClick")

'Defining new workbooks
Dim nwb As Workbook
Dim nsh As Worksheet

'Get unique Region to create workbooks
setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("B:B").Copy setting_Sh.Range("A1")
setting_Sh.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

'Selecting Regions and filtering and copy to New sheet
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 

'Here user has to input the location
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:B").Clear
MsgBox "Done"
End Sub

I tried to make the VBA code as dynamic as possible but you can modify the code as you want.

Download workbook here

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries.