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.
Let’s have a look at the example: You have this type of data sets like above picture. 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.
Here’s a how to apply Excel Advanced filters using a VBA code:
Copy the entire filtered data set 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.
VBA Coding Part:
Copy the below VBA code and paste in 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
All 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.
Now you click on the button you will see that individual sheet is created and records associated with that state are placed on the new sheet. Sheet name is changed as per records.
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. Download the working Excel macro code file from here. You may want to customize this excel advanced filter by changing the filtered column.
Continue learning for free Excel Macro tutorial, automate work and stun your colleagues.
If you’ve tried this, let me know if this helped. Share this article with your friends and family!