fbpx

Excel Advanced Filters to split data into separate sheets using VBA

Excel Advanced filters technique are useful when you deal with a large dataset. In this blog, we will try to cover one of Excel Advanced filter techniques to understand how to split data into a separate sheet through VBA code (it’s a part of our Excel VBA tutorial program).

For instance, we have a huge list of U.S. records and we need to split the data as per state. Now imagine there are almost 51 states in U.S. and you need to apply an Excel Advanced filters and then choose one by one and then copy and paste the same into a new sheet. It will take almost one hour or more as it is very tedious work. Just use the macro code on this page and it will take not more than one minute to complete the task. That’s why VBA code in excel is so powerful.

Excel VBA Tutorial Course OnlineLearn Excel VBA  (Beginner To Advanced)

If you want to be a master at Excel VBA Programming language for Excel 2007, then our Excel VBA macros tutorials will make it easier for you to access it in applications such as Microsoft Office. Come create your own Macros and rule in excel.

Let’s have a look at the example:

Split-data-using-Excel-Advanced-Filter

You have this type of data sets like above picture. However, If you check very carefully then you will see that the 6th column from the left contains the name of the state and you need to separate the records into a new sheet and paste the records on the new sheet.

Recommended:

Since you must have learned how to do Advanced Filter to split data using Excel VBA. Now, you can learn Remove Password From Excel, Using Excel VBA (Coding) or you can learn such more tricks. Learn More


How to Apply Excel Advanced filters using a VBA code:

Copy the entire filtered dataset and paste it into a new sheet. Along with this, change the name of the worksheet based on the filtered tab. For example, if it copies the records of LA (State) and pastes it in a new sheet then it will change the name of a sheet as LA.

You can also split data using Text to column

VBA Coding Part:

Copy the below VBA code and paste into a module.

To do the same please press Alt + F11 and then insert a module and paste the below code.

Sub ExtractToSheets()
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim rData As Range
Dim rfl As Range
Dim state As String
Set ws = ThisWorkbook.Sheets("emp")

With ws
Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 11).End(xlUp))
.Columns(.Columns.Count).Clear
.Range(.Cells(2, 6), .Cells(.Rows.Count, 6).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
'Calls Excel Advanced Filter
For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
state = rfl.Text

If WksExists(state) Then
Sheets(state).Cells.Clear
Else

Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = state
End If

rData.AutoFilter Field:=6, Criteria1:=state
rData.Copy Destination:=Worksheets(state).Cells(1, 1)
Next rfl
End With
ws.Columns(Columns.Count).ClearContents
rData.AutoFilter
End Sub

Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
'Excel advanced filter technique

I have done is created an Excel Advanced filter using a VBA code. All you need to edit the code to select the filtered parameter while calling the Excel Advanced filter and call this macro when you need.

Now go back to your excel sheet and create a shape and assign the code to that shape. Right-click on that shape and then click on assign. Then below box will appear and select the macro name and then click OK.Assign-Macro-help-Excel-Advanced-Filter

Next Step:

Click on the button you will see that individual sheet is created. Also, the records associated with that state are placed on the new sheet. Sheet name is changed as per records.

Separate-Sheet-VBA-Excel-Advanced-Filter Create-Sheet-state-Excel-Advanced-Filter

First of all, I have applied an Excel advanced filter and stored. Then in a variable and then checked whether the state name already exists or not. If the state name exists in the book then it will clear the data on the sheet. And if the sheet does not present in the workbook then it will create a new sheet and paste the data on that sheet. Please practice the same and play with the macro code. separate

Since, You must have learned how to do Advanced Filter to split data using Excel VBA. Now, you can learn Remove Password From Excel, Using Excel VBA (Coding) or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

You may want to customize this excel advanced filter by changing the filtered column.

 Download Example File

Related Items:

Although, Continue learning for free Excel Macro tutorial, automate work and stun your colleagues.

9 Responses
  1. Craig Paveley

    Hi there,
    This worked a treat – however it doesn’t complete through all of my data? Is there a limit to the number of sheets that can be generated or am I missing something in the code?

    Thanks!

  2. Jason

    I am having issues applying this code to my worksheet. Everything is the name except my data field is to change for every partner as opposed to change in state.

    When I execute the code I receive the error

    “Run-time error 1004: That name is already take. Try a different one”

    I have changed the count for cells because my field is a different column number and changed the text as well.

    Sub ExtractToSheets()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rData As Range
    Dim rfl As Range
    Dim state As String
    Set ws = ActiveSheet

    With ws
    Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 26).End(xlUp))
    .Columns(.Columns.Count).Clear
    .Range(.Cells(2, 14), .Cells(.Rows.Count, 14).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

    For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
    partner = rfl.Text

    If WksExists(state) Then
    Sheets(state).Cells.Clear
    Else

    Set wsNew = Sheets.Add
    wsNew.Move After:=Worksheets(Worksheets.Count)
    wsNew.Name = partner
    End If

    rData.AutoFilter Field:=14, Criteria1:=partner
    rData.Copy Destination:=Worksheets(partner).Cells(1, 1)
    Next rfl
    End With
    ws.Columns(Columns.Count).ClearContents
    rData.AutoFilter
    End Sub

    Please help!

Leave a Reply