Advanced Filter Macro Examples

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.

Advanced Filter Setup

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.

Record and Edit Advanced Filter Macro

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.

Record an Advanced Filter Macro

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.

• The original data is on the Orders sheet
• The copied data is on the TopOrders sheet.

Here is the code that was recorded by the Excel macro recorder.

Edit the Advanced Filter Code

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

Problem With Table Slicers

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.

Send List Items to Separate Sheets

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

1) Flexible Filter Macro - For Excel Tables

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.

• Note: This macro is for formatted Excel tables only.

To use the macro, copy the code (shown below), to a regular code module in your workbook.

• Then, select a sheet that has the formatted Excel table you want to filter the data from.
• To run the macro, go to the View tab on the Excel Ribbon
• At the far right, click Macros, then click CreateItemSheets, and click Run

The macro prompts you to enter the name of a column that you want to use for unique items.

• In this screen shot, "region" is entered (upper or lower case does not matter).
• Type a valid column name, then click OK
• A sheet is created for each unique item in that column, with all the data for that item only
  • NOTE: Old item sheets with the same names will be deleted.
  • -- If you want to save the old sheets, rename them
• Click the OK button in the confirmation message

Flexible Filter Macro Code

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

2) Specific Settings Macro

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.

1. Create SHEET - leaves sheets in the existing workbook
2. Create WORKBOOK - sends each sheet to a new workbook, named for Sales Rep

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.

3) Filter & Copy Formatting Macro

When an Advanced Filter extracts data to a different location:

• some of the cell formatting is kept, such as borders, fill colour, and number formatting
• some cell contents are lost, such as notes, formulas, hyperlinks, and conditional formatting.

This "Filter & Copy" example creates a sheet for each sales rep, by:

• applying an Advanced Filter in place
• then copying the visible rows to a new sheet.

This technique keeps the cell formatting, formulas, and other items.

Get the Sample Files

1. Slicer Problem: Download this workbook which shows an example of the problem with table slicers sample file. The zipped file contains macros, so enable macros to test the filter, and see the problem with connected slicers.
2. Send List to Sheet or File (Specific Settings): Download this workbook with specific sheet and column settings set as variables in the macro. Macros send data for each unique item to a new sheet or file. The zipped sample file is in xlsm format, and contains macros.
3. Send List to Sheets (Flexible): Download this workbook with a flexible macro, for Excel tables only. Macro sends data for each unique item to a new sheet or file. Sheets get the unique item names, and old sheets with same names are automatically deleted. The zipped sample file is in xlsm format, and contains macros.
4. Filter & Copy Formatting: Download this workbook with the Filter & Copy macro. Applies Advanced Filter in place, then copies visible rows to a new sheet. That retains cell formatting, formulas, and other items. Old sheets with same names are automatically deleted. The zipped sample file is in xlsm format, and contains macros.

Related Tutorials

Advanced Filter Intro

Advanced Filter Criteria

Advanced Filter Criteria Slicers

Advanced Filter Macro

Excel Slicers Intro

Create Excel Tables