BDHN,
Based on what you've described, I feel a need to help you out further...
OBSERVATION 1)
It appears the reason why you weren't able to get A.C.'s code to work, is because you are not yet aware of what he was referring to by reference to "range names".
A range name is an arbitrary name that you can create/assign to any cell or range-of-cells anywhere in the workbook. So where he referred to "DataList", you needed to create such a name (or you could use your own preference for the name) for the range of cells where your data is located. In your example, you've referred to A2:AG31.
And you would also need to do the same for "Criteria" and "Destination".
Please appreciate the EXTREME SIGNIFICANCE of why it is IMPORTANT to create and use range names... a) It allows you to make your application much more user-friendly for yourself and end-users, and b) with reference to using VBA code (such as what you recorded), such code is referred to as "HARD CODE" - meaning that if and when changes/modifications are made to your application, your code will NOT change. With many smaller applications, they tend to "grow" and therefore you could end up with a larger application where you have several VBA routines that you have "hard coded". Then, when you need to make changes (insertion/deletion of rows, moving data from place to place, etc), you will of course need to review all your code and make whatever changes are required to update your VBA code.
IF "range names" are used, however, there will NOT be any need to modify the VBA code. This is because "internally", Excel maintains a "link" between the names and the cells to which the names have been assigned. Thus, when rows/columns are inserted/deleted, or data moved, the range names are automatically adjusted.
OBSERVATION 2)
I noticed you entered your "criteria" and "extraction" range (or destination range) on the SAME sheet as your data list (or database) - i.e. EVERYTHING on the SAME sheet.
I appreciate you might have had good reason to do this. Depending on the version of Excel you have, you might have encountered two BUGS in the form of FALSE ERROR MESSAGES that tell you: a) It's NOT possible to place your criteria on a different sheet, and b) It's NOT possible to extract data to a SEPARATE sheet.
With older versions of Excel (as well as current), it is not only possible to place criteria on a SEPARATE sheet, but it it actually "PREFERABLE". This is because if you do place your criteria on the SAME sheet, Excel will give you plenty of headaches - because it will attempt to "think for you", and in the process, will cause confusion and errors regarding the use of field names in the criteria, etc. When you place your criteria on a SEPARATE sheet, and if you use formula(s) in your criteria, be aware that Excel will show #NAME? in the cell containing your formula. This is normal. In later versions of Excel, buried in the bowels of the Help function, Excel does mention that this #NAME? is normal in the case of creating "criteria".
It IS possible to extract data to one or more SEPARATE sheets. If you attempt to do so via the menu, as mentioned, you might be stopped by the ERROR MESSAGE. However, be aware that extraction of data to a separate sheet using VBA code (like the example provided by A.C.) works with no problem - provided you first create the range names. The "destination" range (or whatever name you decide to use) can be assigned to ANY worksheet. Normally, it should be assigned to the top row that contains the field names. You can be selective in the field names you use, and you can change the order.
You can set up separate routines that reference the SAME database, but reference different criteria ranges, for extraction of "different" data to different sheets - potentially with different (pre-set) headings and print settings suited for the different types of reports being generated.
I hope this helps get you "up and running" with this powerful component of Excel.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
.AdvancedFilter _