Automate Autofilter Results - Copy to New Sheet

[Guest]

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(xlCellTypeVisible).Copy
Sheets("Skip").Select.Range("A2").Insert Shift:=xlDown

Any help will be most appreciated. Thank you.
Annie

 

[bhawane]

Hi Annie,


Im not entirely sure what you are trying to achieve with this, but i'm sure I have tackled a task similar. If you just want to avoid copying the title into the new sheet, just do:
Selection.offset(1,0).copy

Alternatively you may want to use the for ... each command instead of using the autofilter (depending on how much data you have), then more one line at a time.

Any more help, e-mail me at (e-mail address removed)

 

[Art Farrell]

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

 

[Guest]

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