Advanced Filter Criteria; Copy to another sheet

I wish to filter a table and have the results appear on another sheet by VBA, but I would like to know if there is some way to enter the criteria as a formula instead of a range.

My table is about 15 columns wide, and I wish to filter for all the items in the table column labeled "Key" that are equal to a specified value. I can easily enough start at the target sheet (where I wish the filtered results to appear) and over in cell Z1 label it "Key" (Without quotation marks) and in Z2 enter the key number that will appear in all the records I wish to extract, so the vba for the process would look something like:

Please Login or Register  to view this content.

...but this seems kind of convoluted to me in that I would first have to write the column header and value to Z1 and Z2; it seems we should have a way of telling the CriteriaRange to look for all values in tblPeople[[#Headers],[Key]] that are equal to a value, yet I have not stumbled on the correct syntax if there is one.

Thoughts?

Thank-You, Merry Christmas and Happy New Year!!!

As far as I know. Advanced filter requires ranges to be set up prior and I have not found any workaround.

If you want to do similar operation without criteria range there are several ways to go about it (not Advanced filter).

1. VBA - ADODB code to retrieve recordset matching criteria and write to new sheet
2. PowerQuery - To set up criteria, you can... set filter; modify "M" in advanced editor; set up custom "M" function as separate query and pass parameter; set up named ranges to pass parameter etc.
3. MS Query - Similar to PowerQuery, but all criteria set up either in Editor or via Command Text.
4. VBA - Autofilter and copy/paste

Advantage of Advanced Filter is that once you set up criteria range and headers, it's easy for someone without knowledge of VBA or other tools to change parameter directly within workbook. I often combine it with data validation list and small code to allow user to query info (especially when they want to export data without aggregation).

Thank-You folks, I'll consider what you have provided and post if I find anything interesting that can be of help to anyone in the future in this regard!