Mike
Tested your code, seems working well. Need some time to observe perfectly
and another thing what i observed is i need to press Alt+F8, in order to run
the macro, but what i am looking is the moment i changed the status from w to
r immediately without pressing alt+F8, the data should be copied into the
second sheet.
Please adjust the code as required.
Thanks in advance

"Mike H" wrote:

I forgot,

You never said which column the code "R" was in so I assumed column D.
Change to suit.

Mike

"Mike H" wrote:

Hi,

Right click the sheet tab with the data in, view code and paste this in and
run it

Sub stance()
Dim MyRange
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D1:D" & Lastrow)
For Each C In MyRange
If UCase(C.Value) = "R" Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy
Sheets("Resigned").Range("A1").PasteSpecial
End If
End Sub


Mike
"Vijay DSK" wrote:

Hi all,
i am a novice in Excel. Need your advise in solving a problem.
The issue is I have a sheet with all my employee names with their working
status as "W" for Working "R"for Resigned and "AB" absconding.
I conditionally formatted the cell, as it was filled with Red color where R
is the condition.

Now i am looking a solution, i want to copy that entire row to a sheet by
name "Resigned" where working status is "R".
I am looking for vba code as it could not be viewed by lay men.

Thanks in advance for resolving the issue.