Dave,

This macro assumes that B6 and C6 are the cells to reference BEFORE inserting a new column A:

Sub DaveFilterMacro()
Dim mySht As Worksheet
Dim myNSht As Worksheet
Set mySht = ActiveSheet
Set myNSht = Sheets.Add(Type:="Worksheet")
With mySht
.Range("A5").EntireColumn.Insert
.Range("A5").Value = "Helper"
.Range("A6:A" & .Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _
"=IF(AND(ISNUMBER(MATCH(LEFT(RC[2],3),Sheet1!R2C1:R11C1,0))," & _
"ISNUMBER(MATCH(RC[3],Sheet1!R2C2:R39C2))),""X"","""")"
.Range("A5").AutoFilter Field:=1, Criteria1:="X"
.Range("A5").CurrentRegion.SpecialCells(xlCellType Visible).Copy _
myNSht.Range("A1")
End With
End Sub

HTH,
Bernie
MS Excel MVP


"Dave F" wrote in message
...
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,Sheet1!$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.