VBA Script: How to split data into different sheets based on a value in a column

Lets say you a 10000 row data dump which has atleast 30 types of unique values in a column. Now your task it to split this data into multiple sheets with these sheets being named based on the unique value.

Doing this task manually would take atleast 30 mins and is prone to human error.

So how do we do this using a macro. Here are the steps based on a hypothetical data.

Our data is below. For this exercise prepare something similar on your Excel. 

Now hit Alt+F11 and click on create new module as shown below.

Once you have created new module. On the right hand white space you would need to enter the below VBA code. I will explain you on each line so that you are not simply copy pasting but you know what you are doing.

VBA code:

Sub filter()

Application.ScreenUpdating = False

Dim x As Range

Dim rng As Range

Dim last As Long

Dim sht As String

'specify sheet name in which the data is stored which needs to be split

sht = "Macro 2"

'choose the filter column in the following code

last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row

Set rng = Sheets(sht).Range("B1:I" & last)

'N1 is the column where the code temporarily stores unique values of  the "to-be-split" 'column from previous step, so specify a empty column 

Sheets(sht).Range("B1:B" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True

For Each x In Range([N2], Cells(Rows.Count, "N").End(xlUp))

With rng

.AutoFilter

.AutoFilter Field:=1, Criteria1:=x.Value

.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value

ActiveSheet.Paste

End With

Next x

' Turn off filter

Sheets(sht).AutoFilterMode = False

With Application

.CutCopyMode = False

.ScreenUpdating = True

End With

End Sub

Once you have entered the above code. You can have a button/object linked with this code so that evey time you click the data is split based on the specified column into multiple sheets and with name of the sheet as per the unique value used to split.

If you need a copy of the excel file readymade, please email me at askme.kkhelps@gmail.com

Hope this helps!