Autofilter copy

[Mike Fogleman]

I have a fixed size table of data to autofilter. The data range is
Worksheets("DS Analysis").Range("A1:E25"). Row 1 is headers eg.,
DS Port, 2 Wk Max, %, 2 Wk Avg, %. I need to do 2 separate filter/copy/paste
of values and formats to 2 different destinations. The first filter criteria
is Field:=3, Criteria1:=">=.85". The filtered results from columns A, B, & C
need pasted to G1 (includes headers). The 2nd filter criteria is to ShowAll
on field 3, then Field:=5, Criteria1:=">=.75". The filtered results from
Columns A, D, & E need pasted to J1 (includes headers).
I would like to do this without .Selection if possible, and avoid copy/paste
of entire columns to keep the .UsedRange to a minimum.
Thanks for any code you could provide

Mike F

 

[Dave Peterson]

I would think recording a macro when you do this manually would get you close to
the code you want.

If you try it and have trouble, post back.

 

[Mike Fogleman]

I was having trouble with getting the non-contiguous columns A, D,E.
Sub DSFilter()
Dim srng As Range, drng As Range

Set srng = Worksheets("DS Analysis").Range("A1:E25")
Set drng = Worksheets("DS Analysis").Range("G1")

With srng
.AutoFilter field:=3, Criteria1:=">=.85"
.Columns("A:C").SpecialCells(xlVisible).Copy Destination:=drng
.AutoFilter field:=3
.AutoFilter field:=5, Criteria1:=">=.75"
.Range("A:A,D:E").SpecialCells(xlVisible).Copy
Destination:=drng.Offset(0, 3)
.AutoFilter
End With
End Sub

until I realized that they must now be .Range not .Columns("A, D:E")
thnx
Mike F

 

[Dave Peterson]

How about just doing it steps:

Option Explicit
Sub DSFilter()
Dim srng As Range, drng As Range

Set srng = Worksheets("DS Analysis").Range("A1:E25")
Set drng = Worksheets("DS Analysis").Range("G1")

With srng
.AutoFilter Field:=3, Criteria1:=">=.85"
.Columns("A:C").SpecialCells(xlVisible).Copy _
Destination:=drng

.AutoFilter Field:=3

.AutoFilter Field:=5, Criteria1:=">=.75"
.Range("A:A").SpecialCells(xlVisible).Copy _
Destination:=drng.Offset(0, 3)
.Range("D:e").SpecialCells(xlVisible).Copy _
Destination:=drng.Offset(0, 4)

.AutoFilter
End With
End Sub