Moving to other sheet basis on condition

[Vijay DSK]

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.

 

[Mike H]

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" & 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

 

[Mike H]

I forgot,

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

Mike

 

[Vijay DSK]

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

 

[Vijay DSK]

Mike can you just look into this and help me out.

Thanks in advance

 

[Gord Dibben]

Try this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const MyRange As String = "D"
Dim rng2 As Range
Set rng2 = Worksheets("Resigned").Cells(Rows.Count, 1).End(xlUp) _
..Offset(1, 0)
If Not Intersect(Target, Me.Range(MyRange)) Is Nothing Then
If UCase(Target.Value) = "R" Then
Target.EntireRow.Copy Destination:=rng2
End If
End If
End Sub

This is sheet event code. Right-click on the sheet tab and paste the code
into that module.

Edit MyRange to suit........I used column D as Mike did.

Alt + q to return to the Excel window.

Start pounding r's into the column to have the entire row copied to next
available row in Resigned worksheet.

Note..................will not copy existing r's rows to the new sheet.

Run Mike's code once to achieve that first.


Gord Dibben MS Excel MVP

 

[Vijay DSK]

Gord Dibben,
Thanks for your advise, sorry for the late reply.
The code is working well, but it was copying the data to "resigned" data and
pasting with a row gap from the existing row.
Pls advise. I have two more issues on this but to avoid confusion i will
present them once this issue is sorted.

Thanks in advance

 

[Homer]

How would I modify the code to copy rows to different sheets based on the
contents of the cell.

To keep on the same page as previous:

Say there was another sheet named "absconding" and whenever the "AB" was in
the cell that row is copied to the sheet "absconding". And there is a third
sheet named "working" and whenever the "W" was in the cell that row is copied
to the sheet "working".

I have 4-5 possibilities for the contents of the cell and I want to have 4-5
sheets. Each sheet for each posibility.

 

[Vijay DSK]

Homer,
Sorry for the late reply.
Actually I am looking at different categaries (Working, Resigned and
absconding) to be copied on to different sheets (Sheet names ex: working,
resigned and absconding)

Once again sorry for the late reply.