Transfering certain data for one sheet to another

[Kurt706]

I need help pulling data from “sheet1” if a certain criteria are met and putting the data in “sheet2”. If a person (column A) on sheet1 has a pass (column B) by their name I want to move their name and the word pass to sheet2. I have put in a small example.

Example on sheet1
Column A Column B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Name</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Grade</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Tom </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Frank</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Fail</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Jill</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Fail</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Kathy</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bob</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR></TBODY></TABLE>


Desired results on sheet2
Column A Column B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Name</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Grade</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Tom</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Kathy</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bob</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Pass</TD></TR></TBODY></TABLE>

Thanks

 

[JoeMo]

Try this (assumes your headers are in 1st row on both sheets):

Sub MovePassGrades()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim lRw1 As Long, lRw2 As Long
'Assume all Names/Grades on Sheet1 starting in A2
Set sht1 = ThisWorkbook.Sheets("Sheet1")
lRw1 = sht1.Range("A" & Rows.Count).End(xlUp).Row
Set sht2 = ThisWorkbook.Sheets("Sheet2")
lRw2 = sht2.Range("A" & Rows.Count).End(xlUp).Row
sht2.Range("A1", "B1").Value = Array("Name", "Grade")

With sht1
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A1", "B" & lRw1)
        .AutoFilter field:=2, Criteria1:="pass"
        .Offset(1, 0).Resize(.Rows.Count - 1).Copy sht2.Cells(lRw2 + 1, 1)
    End With
End With
End Sub

 

[Kurt706]

Thanks ...it works, but it delete's the fail ones on sheet1 and I would like to keep them on sheet1.

 

[Marcelo Branco]

Hi,

Maybe these formulas in Sheet2 (Excel 2007 or higher)
A2
=IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Pass",ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($1:1))),"")
confirmed with CTRL+SHIFT+ENTER
copy down

B2
=IF(A2<>"","Pass","")
just ENTER
copy down

HTH

M.

 

[Marcelo Branco]

An alternative formula for all Excel versions

A2
=IF(ROWS($1:1)>COUNTIF(Sheet1!$B$2:$B$100,"Pass"),"",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$B$2:$B$100="Pass",ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($1:1))))
Ctrl+Shift+Enter

B2
same as previous post

M.

 

[JoeMo]

Thanks ...it works, but it delete's the fail ones on sheet1 and I would like to keep them on sheet1.

It doesn't delete them, they are filtered out. I have added a line to the code to unfilter sheet1.

Sub MovePassGrades()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim lRw1 As Long, lRw2 As Long
'Assume all Names/Grades on Sheet1 starting in A2
Set sht1 = ThisWorkbook.Sheets("Sheet1")
lRw1 = sht1.Range("A" & Rows.Count).End(xlUp).Row
Set sht2 = ThisWorkbook.Sheets("Sheet2")
lRw2 = sht2.Range("A" & Rows.Count).End(xlUp).Row
sht2.Range("A1", "B1").Value = Array("Name", "Grade")

With sht1
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Range("A1", "B" & lRw1)
        .AutoFilter field:=2, Criteria1:="pass"
        .Offset(1, 0).Resize(.Rows.Count - 1).Copy sht2.Cells(lRw2 + 1, 1)
    End With
    .AutoFilterMode = False
End With
End Sub

 

[Kurt706]

They both work

Thank you both for the help!