Yeah the data starts in row 6. It's a feed from a database and for some
reason the feed is set up so that the table starts in row 6. Thanks.

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


"Dave Peterson" wrote:

So your real data starts in Row 6?

I used column X to hold the formula--you didn't share what column you used:

Option Explicit
Sub testme()
Dim RptWks As Worksheet
Dim CurWks As Worksheet
Dim LastRow As Long

Set CurWks = Worksheets("sheet2") '????????
Set RptWks = Worksheets.Add

With CurWks
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("x6:x" & LastRow)
.Formula _
= "=IF(AND(ISNUMBER(MATCH(LEFT(B6,3),Sheet1!$A$2:$A$ 11,0))," _
& "ISNUMBER(MATCH(C6,Sheet1!$B$2:$B$39))),""X"","""" )"
.Value = .Value 'makes the filter faster
End With
.Range("x6:X" & LastRow).AutoFilter field:=1, Criteria1:="x"
.AutoFilter.Range.EntireRow.Copy _
Destination:=RptWks.Range("a1")
End With

End Sub

If you used a different column you'll have a few spots to fix. And if your data
starts in a different row, you'll want to change the formula, too.

Use the number of the first row getting the formula.


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,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.

--

Dave Peterson