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

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

On Nov 5, 9:04 am, tigger <tig...@discussions.microsoft.com> 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

Hi
Try this
sh.Range("Status").AutoFilter Field:=1, Criteria1:="Open"

Help says Field is optional, but I got an error if I didn't specify
it.

regards
Paul

Thanks guys - worked perfectly!

The other thing I want to do is copy the visible cells in a selected range -
code below.

Any ideas where I should put .SpecialCells(xlCellTypeVisible)?

Thanks

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

sh.Range("A1").AutoFilter Field:=7, Criteria1:="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

"(E-Mail Removed)" wrote:

> On Nov 5, 9:04 am, tigger <tig...@discussions.microsoft.com> 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
>
> Hi
> Try this
> sh.Range("Status").AutoFilter Field:=1, Criteria1:="Open"
>
> Help says Field is optional, but I got an error if I didn't specify
> it.
>
> regards
> Paul
>
>

Thanks for the feedback :-)

Does this work for you?

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

.Range("A1").AutoFilter Field:=7, Criteria1:="Open"

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

'Copy range and paste into Summary as values
With .Range("D2:H2", .Cells(.Rows.Count,
"D").End(xlUp)).SpecialCells(xlCellTypeVisible)
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 = .Name
End If
End With
Next sh

btw, I've wrapped this whole section of code in a "with sh" clause.
I've added the xltypevisible in where I think it should go but with this
done I'm not sure if it will still like the following resizing section of
"(.Rows.Count, .Columns.Count)".

hth
Rob

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


"tigger" wrote:

> Thanks guys - worked perfectly!
>
> The other thing I want to do is copy the visible cells in a selected range -
> code below.
>
> Any ideas where I should put .SpecialCells(xlCellTypeVisible)?
>
> Thanks
>
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> sh.AutoFilterMode = False
>
> sh.Range("A1").AutoFilter Field:=7, Criteria1:="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
>
> "(E-Mail Removed)" wrote:
>
> > On Nov 5, 9:04 am, tigger <tig...@discussions.microsoft.com> 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
> >
> > Hi
> > Try this
> > sh.Range("Status").AutoFilter Field:=1, Criteria1:="Open"
> >
> > Help says Field is optional, but I got an error if I didn't specify
> > it.
> >
> > regards
> > Paul
> >
> >

Needed some tweaking but works great!

Thanks for your time

With sh.Range("D2:H2", sh.Cells(sh.Rows.Count,
"D").End(xlUp)).SpecialCells(xlCellTypeVisible)
DestSh.Cells(Last + 1, "B").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

"broro183" wrote:

> Thanks for the feedback :-)
>
> Does this work for you?
>
> For Each sh In ThisWorkbook.Worksheets
> With sh
> If .Name <> DestSh.Name Then
> .AutoFilterMode = False
>
> .Range("A1").AutoFilter Field:=7, Criteria1:="Open"
>
> Last = LastRow(DestSh)
> shLast = LastRow(sh)
>
> 'Copy range and paste into Summary as values
> With .Range("D2:H2", .Cells(.Rows.Count,
> "D").End(xlUp)).SpecialCells(xlCellTypeVisible)
> 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 = .Name
> End If
> End With
> Next sh
>
> btw, I've wrapped this whole section of code in a "with sh" clause.
> I've added the xltypevisible in where I think it should go but with this
> done I'm not sure if it will still like the following resizing section of
> "(.Rows.Count, .Columns.Count)".
>
> hth
> Rob
>
> __________________
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> "tigger" wrote:
>
> > Thanks guys - worked perfectly!
> >
> > The other thing I want to do is copy the visible cells in a selected range -
> > code below.
> >
> > Any ideas where I should put .SpecialCells(xlCellTypeVisible)?
> >
> > Thanks
> >
> > For Each sh In ThisWorkbook.Worksheets
> > If sh.Name <> DestSh.Name Then
> > sh.AutoFilterMode = False
> >
> > sh.Range("A1").AutoFilter Field:=7, Criteria1:="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
> >
> > "(E-Mail Removed)" wrote:
> >
> > > On Nov 5, 9:04 am, tigger <tig...@discussions.microsoft.com> 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
> > >
> > > Hi
> > > Try this
> > > sh.Range("Status").AutoFilter Field:=1, Criteria1:="Open"
> > >
> > > Help says Field is optional, but I got an error if I didn't specify
> > > it.
> > >
> > > regards
> > > Paul
> > >
> > >