hi Tigger,

I think you may be missing some important syntax from this line try changing
it to reflect the following which I have copied from Excel 2003 Help files
for "Autofilter Method & "Autofilter Object" respectively:

Syntax = expression.AutoFilter(Field, Criteria1, Operator, Criteria2,
VisibleDropDown)
example = w.Range("A1").AutoFilter field:=1, Criteria1:="S"

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

"tigger" wrote:

HI there,

I'm trying to apply an autofilter to each sheet in my workbook to copy data
to a summary sheet based on criteria.

I keep getting an error on 'sh.Range("Status").AutoFilter , "Open"'

Can anyone help?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
sh.AutoFilterMode = False

sh.Range("Status").AutoFilter , "Open"

Last = LastRow(DestSh)
shLast = LastRow(sh)

'Copy range and paste into Summary as values
With sh.Range("D2:H2", sh.Cells(sh.Rows.Count, "D").End(xlUp))
DestSh.Cells(Last + 1, "B").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Copy sheet name to Summary column A
DestSh.Cells(Last + 1, "A").Value = sh.Name
End If
Next

Thanks