What it does
Uses a settings sheet (Bulk_Filter) to loop through a defined list of sheets/ranges to apply filter criteria.  The specified sheets/ranges are often identical in structure and are the result of running a report that splits different locations, cost centers, etc. out to different sheets.

When to use it
When you want to quickly apply filters on several different sheets/ranges within the same file, whether or not the filter criteria is the same for the different sheets/ranges.

Why to use it
It’s an efficient way to apply filtering across several sheets.  This is especially useful for repetitive filtering actions – those you perform every day, week, month, etc. to ensure that all ranges are filtered with the intended filter criteria.

Default shortcut
None

Other Details

• Category: Filter and Sort / Filter
• Difficulty: 4/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 180 seconds saved each time used)
• Type: Bulk
• Date added: 9/6/2018
• Tags: Filter, bulk, sheets

Related Macros
Filter Picker

Other Articles
None

None

Prerequisites
Identify the sheet(s) and range(s) you want to apply filters to.

Instructions
After you have identified the sheet(s) and range(s) you want to apply filters to, run the Bulk Filter macro.  The first time you run it, it will create a sheet called Bulk_Filter.  This is where you’ll configure all the ranges you want to apply filters to.  These are the columns within the Bulk_Filter sheet you’ll want to fill in:

• Column A – Range name (required): enter the range name corresponding to the AutoFilter area.  To quickly build a list of named ranges, consider using the Bulk Edit Named Ranges macro.
• Column B – Sheet name (required) – enter the sheet name containing data you want to filter.
• Column C – Column (required) – enter the letter of the column that should be filtered.
• Column D – Clear filters (optional): enter the word “Clear” to clear any existing filters before applying any new filters to the sheet.
• Column E – Filter criteria (required): enter the filter criteria with operator sign(s) (i.e. “>=1”) OR enter multiple values separated by the pipe (“|”) character (with no operator signs). If a second filter criteria is entered in column G, the first filter criteria will NOT be processed as multiple values.
• Column F – And/Or (optional): enter “And” or “Or” to apply a second filter criteria.
• Column G – Filter criteria 2 (optional): enter the second filter criteria (i.e. “>=1”). If using a second filter criteria, you must enter “And” or “Or” in column F.
• Column H – Show/Hide Arrows (optional): enter “Show” to show the filter arrows for all columns (in case previously hidden), enter “Hide” to hide the filter arrows for all columns, or leave blank to ignore. Note that if filtering multiple columns in the same sheet, the last column for the sheet will be used for this setting.

To apply filter criteria to more than one column within the same sheet/filter range, populate another row in the Bulk_Filter sheet.  Enter the columns from left to right, and do not put “Clear” in column D (for criteria within the same sheet/filter range) or previous column filters will be cleared.

To automatically populate the Bulk_Filter sheet based on criteria you have in place within the active workbook, run the Bulk Filter macro, and click “Yes” at the prompt asking if you want to populate the sheet listing out the filter criteria you have in place.

You can save the file you are using with the Bulk_Filter sheet as one of up to five templates.  When you save it as a template, you are prompted whether or not to leverage a template any time you run the Bulk Filter macro.  When you leverage one, the contents of the settings sheet (Bulk_Filter) are copied to the active workbook and the contents are processed (bulk filters applied).  Follow the instructions in cell L1 of the Bulk_Filter sheet to save a template.