Advanced filters using a macro

To find learners and information according to more than one criteria - for example, all learners doing Swimming and Choir you must set up a manual filter.

Copy the heading row and place one copy on Row 1, starting at cell G1, and another copy four rows below that, starting in cell G6.

Type the search criteria in the cells in the range G1:K2.

To run the filter, choose Data, Filter, Advanced Filter.

In the List Range box select the area to be filtered.

In the Criteria Range box select the filter criteria area.

To filter the list by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows. Then press OK.

Create a Macro

You can record a macro to run this filter. To do this, choose Tools, Macro, Record New Macro.

Type the macro name RunAdvancedFilter, and from the Store macro in: dropdown list, choose This Workbook and click OK.

While the macro is recording, run the filter by selecting Data, Filter, Advanced Filter, choose the Copy to another location option, and click OK.

Now click the Stop Recording button on the Stop Recording toolbar, or choose Tools, Macro, Stop Recording.

To run the Macro select Tools, Macro, Macros and select the macro to run.

Another way to run the macro is to add a button to your workbook to run the macro.

Test the macro by altering the criteria in the criteria area.

To assign a macro to a button

To add a macro to an Excel toolbar, right-click the toolbar and choose Customize.

Choose the Commands tab and from the Categories list select Macros.

From the Commands list box drag the Custom Button on to the toolbar and right-click it.

Choose Assign Macro and, from the Assign Macro dialog box select the macro for example, RunAdvancedFilter and click OK.

Right-click on the New button and choose either Edit Button Image to create your own custom button face or Change Button Image to choose one from the list.

When you're done, select Close to return to your file.

Based on ideas from Helen Bradley, an authority on the web on Office packages.