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

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

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