Well you can learn how to do Advanced Filters with VBA by examining
the code ;) Then make any edits to the code you need. Using built-in
functionality is generally desirable where possible for speed and
maintenance.


On Mar 1, 6:20 pm, Dave F wrote:
That's an interesting suggestion, thanks. I was hoping to avoid the macro
recorded and learn some VBA code with this exercise, but this method is
definitely easier.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.



"Lori" wrote:
You could try entering the AND(...) part of your formula as a
calculated criteria to the right of your data, in Z6 say.

Then on a new sheet choose Advanced Filter with list range List!
a5:y65536, criteria List!z5:z6 and Copy to a1 on the new sheet.

This should also be recordable as a macro.

On 1 Mar, 16:37, Dave F wrote:
I have a table of between 50,000 and 60,000 records from which I have to
extract several hundred records. The following function, filled down a
helper column, is one way of filtering these records (via the Auto-Filter
tool):

=IF(AND(ISNUMBER(MATCH(LEFT(B6,3),Sheet1!$A$2:$A$1 1,0)),ISNUMBER(MATCH(C6,S**heet1!$B$2:$B$39))),"X" ,"")

B6 is an account number, whose three left digits signify what type of
account it is. A2:A11 in Sheet1 is the list of those three left digits I
want to extract. C6 is an expense code; B2:B39 in Sheet1 is a list of those
expense codes I want to extract. One would run the AutoFilter on the "X"
values returned by the above formula.

However, what I would like to do is have a macro which runs the same logic,
copies the filtered records, and pastes them in a new sheet. Then all I have
to do is attach that macro to a button.

How to do this?

Thanks for any insight.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.- Hide quoted text -

- Show quoted text -