|
Home > Filters > Advanced > Macros Advanced Filter Macro ExamplesAdvanced 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.

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Advanced Filter Criteria Slicers
Last updated: December 27, 2024 12:12 PM