Automate Autofilter Results - Copy to New Sheet
 

Hello All! I hope someone can help. I have used the macro recorder, and can
do some editing safely, but I am stuck. I need to run the autofilter on a
field several times looking for "begins with" and "contains" conditions
stored in another Excel file. I don't mind pasting all these conditions into
the code, but there may be a more efficient way?

The main problem is that I cannot copy to a new sheet and insert the copied
cells to that sheet (shift cells down - or paste append) without including
the Autofilter Row (1) and blank cells. I am filtering and copying from a
sheet named "Good" to a sheet named "Skip". This is my failing code so far:

Columns("A:AJ").Select
Selection.AutoFilter Field:=5, Criteria1:="=zz*", Operator:=xlAnd
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Copy
Sheets("Skip").Select.Range("A2").Insert Shift:=xlDown

Any help will be most appreciated. Thank you.
Annie

Automate Autofilter Results - Copy to New Sheet
 

Hi Annie,

Assuming your range is contiguous you could use the CurrentRegion rather
than full Columns. If not, change back. Also, I assumed you wanted to append
to the next empty row on "Skip".

Sub AFAnn()
Dim rng As Range

Sheets("Good").Select
Set rng = Sheets("Good").Range("A1").CurrentRegion
rng.AutoFilter Field:=5, Criteria1:="=zz*"

'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)

'copies to the first empty row in Column A
rng.Copy Sheets("Skip").Range("A65536").End(xlUp).Offset(1, 0)
Selection.AutoFilter
End Sub

CHORDially,
Art Farrell


"Annie" wrote in message
...
Hello All! I hope someone can help. I have used the macro recorder, and
can
do some editing safely, but I am stuck. I need to run the autofilter on a
field several times looking for "begins with" and "contains" conditions
stored in another Excel file. I don't mind pasting all these conditions
into
the code, but there may be a more efficient way?

The main problem is that I cannot copy to a new sheet and insert the
copied
cells to that sheet (shift cells down - or paste append) without including
the Autofilter Row (1) and blank cells. I am filtering and copying from a
sheet named "Good" to a sheet named "Skip". This is my failing code so
far:

Columns("A:AJ").Select
Selection.AutoFilter Field:=5, Criteria1:="=zz*", Operator:=xlAnd
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Copy
Sheets("Skip").Select.Range("A2").Insert Shift:=xlDown

Any help will be most appreciated. Thank you.
Annie

Automate Autofilter Results - Copy to New Sheet
 

Hi Art,
Your solution provided me with the perfect framework for a solution. I added
some error trapping, and I'm on my way! Thank you so much.
Annie


"Art Farrell" wrote:

Hi Annie,

Assuming your range is contiguous you could use the CurrentRegion rather
than full Columns. If not, change back. Also, I assumed you wanted to append
to the next empty row on "Skip".

Sub AFAnn()
Dim rng As Range

Sheets("Good").Select
Set rng = Sheets("Good").Range("A1").CurrentRegion
rng.AutoFilter Field:=5, Criteria1:="=zz*"

'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)

'copies to the first empty row in Column A
rng.Copy Sheets("Skip").Range("A65536").End(xlUp).Offset(1, 0)
Selection.AutoFilter
End Sub

CHORDially,
Art Farrell


"Annie" wrote in message
...
Hello All! I hope someone can help. I have used the macro recorder, and
can
do some editing safely, but I am stuck. I need to run the autofilter on a
field several times looking for "begins with" and "contains" conditions
stored in another Excel file. I don't mind pasting all these conditions
into
the code, but there may be a more efficient way?

The main problem is that I cannot copy to a new sheet and insert the
copied
cells to that sheet (shift cells down - or paste append) without including
the Autofilter Row (1) and blank cells. I am filtering and copying from a
sheet named "Good" to a sheet named "Skip". This is my failing code so
far:

Columns("A:AJ").Select
Selection.AutoFilter Field:=5, Criteria1:="=zz*", Operator:=xlAnd
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Copy
Sheets("Skip").Select.Range("A2").Insert Shift:=xlDown

Any help will be most appreciated. Thank you.
Annie