Macro to filter sheet and copy to another

[Gareth]

I want to used AutoFilter to filter column T on Sheet1 for the word
'Amended'.

If there are any found, I would like to copy only certain columns to Sheet2.

The columns are A:C, G:M, Q:S

Thanks in advance.

Gareth

 

[Tom Ogilvy]

Sub CopyData()
With Worksheets("sheet4")
.Range("A1").AutoFilter Field:=20, Criteria1:="Amended"
Set rng = .AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng1 = Intersect(rng, .Range("A:C,G:M,Q:S"))
rng1.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End With
End Sub

 

[Alan Beban]

Well, it doesn't use Autofilter, but if the functions in the
downloadable file at http://home.pacbell.net/beban are available to your
workbook, and assuming your unfiltered data consists of 50 rows in a
range named "datarange", you can array enter into the following ranges
on the sheet to which you want to copy the data:

A1:C50
=SubArray(ArrayRowFilter1(datarange,20,"Amended"),1,3,1,ArrayCountif(Sheet1!T1:T9,"Amended"))

D1:J50
=SubArray(ArrayRowFilter1(datarange,20,"Amended"),7,14,1,ArrayCountif(Sheet1!T1:T9,"Amended"))

K1:M50
=SubArray(ArrayRowFilter1(datarange,20,"Amended"),17,19,1,ArrayCountif(Sheet1!T1:T9,"Amended"))

#N/A! will be returned to the extra irrelevant rows.

Alan Beban