The Real Workhorse xlFilter Copy with All Records Instead of Unique Records Only

The examples at the beginning of this chapter talked about using xlFilterCopy to get a unique list of values in a field. We used unique lists of customer, region, and product to populate the list boxes in our report specification userforms.

A more common scenario, though, is to use an advanced filter to return all records that match the criteria. After the client selects which customer to report, an advanced filter can extract all records for that customer.

In all these examples, you want to leave the Unique Records Only check box unselected. You do this in VBA by specifying Unique:=False as a parameter to the AdvancedFilter method.

This is easy to do and there are some powerful options. If you need only a subset of fields for a report, then copy only those field headings to the output range. If you want to resequence the fields to appear exactly as you need them in the report, you can do this by changing the sequence of the headings in the output range.

I will walk you through three quick examples to show the options available.

0 0

Post a comment