Autofilter and VBA

[haas786]

Hey all,

I was wondering if you could help me with a VBA problem. I need to
program a macro which would essentially Autofilter a large amount of
information based on two criteria. The critera is listed in two cells
(in cells on another tab/worksheet named "Claims" - cells G2 and G3.)
The values in these cells match entries in two of the columns being
filtered.

So, for example, cell G2 in worksheet "Claims" = "Auto" and cell G3 =
"NJ" - I need the program to filter the data in my other worksheet
("Data") by the two cvalues listed above. Once I get the filter to
work, I need to copy all this data and paste it into a new workbook.

I hope i explained the above well enough to allow you to help me. If
you have any further questions, please ask. Thank you in advance.

-Haas

 

[JW]

You didn't say which columns to filter the data sheet on, so I used D
and F (4 and 6). To do different columns, just change the Field
property of the AutoFilter.
Sub tryThis()
Dim ws As Worksheet
With Sheets("Data")
.AutoFilterMode = False
.Cells.AutoFilter Field:=4, _
Criteria1:=Sheets("Claims").Range("G2").Text
.Cells.AutoFilter Field:=6, _
Criteria1:=Sheets("Claims").Range("G3").Text
Set ws = Worksheets.Add(Before:=Sheets("Data"))
.UsedRange.Copy ws.Range("A1")
.AutoFilterMode = False
End With
Set ws = Nothing
End Sub

 

[Bill Renaud]

JW,

Won't this copy the ENTIRE worksheet? The OP only wanted the filtered data
to be copied? You used the line:

.UsedRange.Copy ws.Range("A1")

 

[JW]

JW,

Won't this copy the ENTIRE worksheet? The OP only wanted the filtered data
to be copied? You used the line:

.UsedRange.Copy ws.Range("A1")

Bill, actually no. It will only copy the filtered data. It's one of
those crazy Excel things where it seems like one bit of code would
cause one thing to happen, but it actually works for some reason.
Give it a go and let me know if it works for you.

Regards
-Jeff-

 

[Bill Renaud]

Yep, it works in Excel 2000 (SP-3) (copying filtered data)!

I am VERY skeptical of this "feature" (bug?), though. This behavior is not
documented in either the UsedRange property or the Copy method! The caveat
is that it only works if the rows were hidden by using AutoFilter. If the
rows were hidden using the normal Format|Row|Hide command, then ALL of the
data is copied, as I first mentioned. This is true, even though AutoFilter
essentially works by simply hiding the rows not wanted!

 

[haas786]

Yep, it works in Excel 2000 (SP-3) (copying filtered data)!

I am VERY skeptical of this "feature" (bug?), though. This behavior is not
documented in either the UsedRange property or the Copy method! The caveat
is that it only works if the rows were hidden by using AutoFilter. If the
rows were hidden using the normal Format|Row|Hide command, then ALL of the
data is copied, as I first mentioned. This is true, even though AutoFilter
essentially works by simply hiding the rows not wanted!

Thanks guys! JW, the code worked perfectly - it achieved the desired
results. I appreciate all the help and I'll make sure to keep the help
going for others.