Autofilter to copy and paste to different sheet based on 2 criterias

Question

I'm not really sure how to use Autofilter for this but I am creating a new worksheet and I'm trying to filter another sheet based on two criterias. I then need to copy the values from only 2 of the columns from that worksheet to the new worksheet. This is the code I have so far:

Private Sub Workbook_Open()

Dim ws As Worksheet
Set sheet = Sheets.Add(After:=Sheets(Worksheets.Count))

ActiveSheet.Range("A11").Value = "Projects in Loss:"

ActiveWorkbook.Sheets("Fielding").Activate

ActiveSheet.ShowAllData

ActiveWorkbook.Sheets("Fielding").Activate

ActiveSheet.ShowAllData

Selection.AutoFilter field:=15,   
Criteria:=ActiveWorkbook.Sheets(Target).Range("A3").Value, Operator:=xlAnd

Selection.AutoFilter field:=21, Criteria:="<30%" & ">0"

ActiveSheet.Columns("A").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

ActiveWorkbook.Sheets(ws).Activate
ActiveSheet.Range("A12").PasteSpecial xlPasteValues
Application.CutCopyMode = False

ActiveWorkbook.sheet ("Fielding")
ActiveSheet.Columns("U").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

ActiveWorkbook.Sheets(ws).Activate
ActiveSheet.Range("B12").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

It keeps giving me a run time error '1004'. Does anyone know what I'm doing wrong or could direct me in the right direction?

Answer 1

Selection.AutoFilter field:=21, Criteria:="<30%" & ">0"

The way to do this is by putting two different criteria with xlAnd operator

Selection.AutoFilter field:=21, Criteria1:=">0", Operator=xlAnd, Criteria2 = "<0.3"

Also, there's no parameter Criteria in the AutoFilter method. It's Criteria1 and optionally another parameter Criteria2 (change Criteria to Criteria1 at other places in your code).

Working with Selection is hazardous and source of bugs. You should really try to rewrite your code without the Selection stuff. Take a look at How to avoid using Select in Excel VBA macros