ExcelBanter - Autofilter on non-active sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Autofilter on non-active sheet (https://www.excelbanter.com/excel-programming/400576-autofilter-non-active-sheet.html)

Autofilter on non-active sheet

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

Autofilter on non-active sheet

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

Autofilter on non-active sheet

On Nov 5, 9:04 am, 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

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

Autofilter on non-active sheet

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

" wrote:

On Nov 5, 9:04 am, 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

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

Autofilter on non-active sheet

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

" wrote:

On Nov 5, 9:04 am, 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

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

Autofilter on non-active sheet

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

" wrote:

On Nov 5, 9:04 am, 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

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