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

**loneman** · 01-04-2012, 03:01 PM

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

**arlu1201** · 01-06-2012, 06:18 AM

Try this code. I have not used autofilter but the code will go thru each row and action accordingly.

Option Explicit
Dim lrow As Long
Dim i As Long

Sub copy_data()

With Worksheets(1)
    lrow = .Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lrow
        If .Range("C" & i).Value = "Yes" And .Range("D" & i).Value = "N/A" Then
            Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Range("A" & i).Value
            Worksheets(2).Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Not Taken Exam"
            Worksheets(2).Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Date
        End If
    Next i

End With

End Sub