Data transfer as per my condition

[AKGOEL]

I HAVE THE DATA IN THE FOLLOWING FORMAT ON SHEET1

S.N0	NAME	PASSPORT YES/NO
1	AK	YES
2	BK	YES
3	CK	NO
4	DK	YES
5	EK	NO
6	FK	YES
7	GK	NO
8	HK	YES
9	IK	NO
10	JK	YES
12	KK	NO
13	LK	YES
14	MK	YES

<tbody>
</tbody>

RESULT REQUIRED IN SHEET 2

S.NO	NAME	PASSPORT YES/NO
1	AK	YES
2	BK	YES
3	DK	YES
4	FK	YES
5	HK	YES
6	JK	YES
7	LK	YES
8	MK	YES

<tbody>
</tbody>

I WANT TO TRANSFER MY DATA FROM SHEET 1 TO SHEET 2 ,THOSE NAMES WHOSE AGAINST WRITTEN YES IN THIRD COLOUMN

 

[Trevor G]

Welcome to the forum,

Use a Filter on the Passport Yes/No to Yes then copy the data and paste over to sheet 2.

 

[AKGOEL]

if i add some data in sheet1 than it should do automatically in sheet 2

 

[Trevor G]

You would need to use the worksheet code so use the right mouse button on the tab name and select View Code then at the top click first drop down on the left and select Worksheet then on the right drop down select Change then you can use this code. You may have to adjust the Column letter, and also this is for new data not current data. So you might need to do the filter first as previous mentioned to get the current list on to sheet 2.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim B As Range
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub
For Each B In Intersect(Target, Me.Range("c:c")).Cells
If B.Text = "Yes" Then
B.EntireRow.Copy Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
'C.EntireRow.Delete
End If
Next
End Sub