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
|