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
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
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks