Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Filter on active cell within autofilter | Excel Programming | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
autofilter on a not-active sheet | Excel Programming | |||
How to count rows in a user-defined AutoFilter or AdvancedFilter is active? | Excel Programming |