Copy and paste autofilter data based on multiple criteria to another worksheet

Hi, I am working on a spreadsheet that I need to filter some records based on multiple criteria and copy the resulting records to another worksheet starting from last empty row. Any help of coding this would be appreciated.

Here is what I am trying to do:

Sheet1: I have the following data

ID | Name | Pass/Fail (Yes/No) | Grade
2............Jason............Yes.......................80
67..........John..............No........................45
34..........Mary.............Yes.......................94
49..........Kelly..............Yes.......................N/A
56..........Rey...............Yes.......................N/A



Sheet2: I have the following headings

ID | Reason | Date
78.............Not taken the exam.......2/12/2011
95.............Postpone the exam........4/12/2011


I wanted to filter sheet1 based on “Yes” from Pass/Fail field and N/A in grade
field (Kelly and Rey would result..) and get just the id numbers 49 and 56 add it
to sheet2. But in the copy process I wanted to add reason as “Not taken exam”
and date as today’s date.

After the copy process Sheet2: should look like as follows:

ID | Reason | Date
78.............Not taken the exam........2/12/2011
95.............Postpone the exam.........4/12/2011
49............Not taken the exam........01/4/2012
56.............Not taken the exam........01/4/2012


I am working on following code but I get errors. Is there any other way to do this? Thanks a bunch!!

Dim rngDest As Range
Set rngDest = worksheets(“Sheet2”).Range(“A” & CStr(Application.Row.Count)).End(xlUp).Offset(1,0)

With ActiveSheet

If WorksheetFunction.CountIFS(.columns(3), “Yes”, .Columns(4), “N/A”) <> ) then
.AutoFilterMode = False
.Range (“A15:A250”).AutoFilter Field:=3, Criteria1:”Yes”, Operator:=xlAnd
.Range (“A15:A250”).AutoFilter Field:=4, Criteria1:”N/A”
ActiveSheet.UsedRange.Copy Destination:rngDest
.AutofilterMode = False
.Application.CutCopyMode = False
End If
End With