Advanced Filter macros to send data from a main list, to separate sheets - one for each sales rep, or region, or other unique items. See how to record and edit an Advanced Filter macro.
An Advanced Filter can show specific data from a list, by applying criteria. Follow the instructions in the Advanced Filter Introduction page, to see how to set up a Criteria Range, and an Extract Range.
In the screen shot below, there is a criteria range in cells B1:D2, using headings that match columns in the table below.
The green "Filter" button will run a filter macro, to send the matching records to a location on another worksheet.
The easiest way to get started with Advanced Filter programming is to record a macro, while you manually apply a filter. Watch this video to see the steps, and the revised code is shown below.
There are instructions here for recording and running a macro.
In this example, a macro was recorded while running an Advanced Filter to copy the top orders onto a different worksheet.
Here is the code that was recorded by the Excel macro recorder.
After you record the macro, you can edit the recorded code, to make it flexible, and to remove any unnecessary lines of code.
The list range in the recorded code is set as "A1:D15". That was changed to Range("A1").CurrentRegion, so the range will automatically adjust, if rows are added or removed.
The other key change was to add the sheet name -- Sheets("Orders") -- for the CopyToRange. Then, if the destination sheet is not active when the macro runs, it will still work correctly.
A few unnecessary lines were also deleted, such as the comment lines, that start with an apostrophe, and the lines that select a cell.
Here is the code, after making a few changes to it, as shown in the video. A few extra line breaks were added, so the code would fit better on this page.
Sub TopOrderFilter() Sheets("Orders").Range("A1") _ .CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Orders") _ .Range("F1:F2"), _ CopyToRange:=Sheets("TopOrders") _ .Range("A1:D1"), _ Unique:=False End Sub
In Excel 2013, you can connect a Slicer to a formatted table. A table's heading row automatically has filter arrows added, but you can still use an Advanced Filter to pull data from the table.
However, if there is a Slicer connected to the table, and the active cell is within the formatted table, an Advanced Filter macro won't run -- it will show an error message.
Make sure that the active cell on the formatted table's sheet is outside of the table, before you run the advanced error.
To see the problem, you can download the "Problem with table slicers" sample file, from the Download section, below.
With an Advanced Filter, you can quickly send a data from a list, based on criteria. For example, in a list of sales orders, create a separate list for each sales rep, with their orders only.
In this section, there are 3 different types of Advanced Filter macros:
-- 1) Table Filter Macro: Macro prompts for setup details
-- 2) Specific Settings Macro: Setup details are entered in the macro code
-- 3) Filter & Copy Formatting: Keeps original formatting and formulas
The macro prompts for the column name that you want to use, and creates a new sheet with the data for each unique item in that column.
To use the macro, copy the code (shown below), to a regular code module in your workbook.
The macro prompts you to enter the name of a column that you want to use for unique items.
Here is the macro code for the Flexible Filter macro. Copy this code to a regular code module in your workbook.
Sub CreateItemSheets() 'from contextures.com Dim wsA As Worksheet Dim wsTemp As Worksheet Dim wsNew As Worksheet Dim wbNew As Workbook Dim myList As ListObject Dim rngList As Range Dim myCol As Range Dim ListHead As Range Dim myPaste As Range Dim colHead As String Dim shName As String Dim colNum As Long Dim r As Integer Dim c As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Set wsA = ActiveSheet On Error Resume Next Set myList = wsA.ListObjects(1) On Error GoTo errHandler If myList Is Nothing Then MsgBox "No Excel table on this sheet" GoTo exitHandler End If Set rngList = myList.Range 'extract list of unique items in specified column 'colHead = "VidID" colHead = InputBox("What is the column name?" _ & vbCrLf _ & "Note:" _ & vbCrLf _ & "Old item sheets with same names" _ & vbCrLf _ & "will be deleted", _ "Column Name") Set ListHead = myList.HeaderRowRange.Cells On Error Resume Next colNum = WorksheetFunction.Match(colHead, ListHead, 0) If colNum = 0 Then GoTo errHandler Set myCol = rngList.Columns(colNum) On Error GoTo errHandler If myCol Is Nothing Then MsgBox "No column has that name. Please try again" GoTo exitHandler End If Set wsTemp = Sheets.Add Set myPaste = wsTemp.Range("A1") myCol.AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=myPaste, Unique:=True r = myPaste.CurrentRegion.Rows.Count 'set up Criteria Area wsTemp.Range("C1").Value = colHead For Each c In wsTemp.Range("A2:A" & r) 'add item name to criteria area 'use equal sign to ensure exact match wsTemp.Range("C2").Value = _ "=""="" & " & Chr(34) & c.Value & Chr(34) shName = Left(c.Value, 31) 'delete existing sheet if necessary On Error Resume Next Worksheets(shName).Delete On Error GoTo errHandler 'add new sheet and run advanced filter Set wsNew = Sheets.Add wsNew.Move After:=Worksheets(Worksheets.Count) wsNew.Name = Left(c.Value, 31) rngList.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=wsTemp.Range("C1:C2"), _ CopyToRange:=wsNew.Range("A1"), _ Unique:=False wsNew.Rows(1).Cells.Font.Bold = True wsNew.Columns.EntireColumn.AutoFit Next wsA.Select Application.ScreenUpdating = True MsgBox "Sheets have been created" exitHandler: On Error Resume Next Application.DisplayAlerts = False wsTemp.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub errHandler: MsgBox "Could not create sheets" Resume exitHandler End Sub
In this example, the sheet name, column number, and other details, are entered in the macro code.
In the Send List to Sheet or File sample file below, there are two macros that create a separate sheet of orders for each sales rep. Each sheet has the sales rep's name on its sheet tab.
You can see the code for both macros in the sample file, and modify the column numbers, or other details, to match your data layout.
When an Advanced Filter extracts data to a different location:
This "Filter & Copy" example creates a sheet for each sales rep, by:
This technique keeps the cell formatting, formulas, and other items.
Last updated: December 14, 2021 2:28 PM