Copy Row to New Sheet based on Conditional

[Reena1024]

Hey!

I have a sheet with a drop down box in column F. In the drop down box, there are 3 possible things it can say.."Current","Pending","Closed"

I want to be able to COPY the row over if the status of the row is "Current".

It would also be completely awesome if in the first Sheet, the status changed to either "Pending" or "Closed" that the row gets deleted automatically from Sheet 4 (which is where I want it copied to).

When I copy it over, I want to be able to outline the row and if the status changes to "Closed" or "Pending", I'd like it to just delete the whole outline.

The second part is not necessary, but would really make my life a breeze.

The thing is, there are like 100 records in the file, and all the ones that have the word "Current" in Row F on Sheet 1, do I want moved to Sheet 4.

Is this possible? Can someone help me with this?

 

[acw]

Hi

The following sheet module will copy the data from the existing sheet to sheet4 whenever the entry in column F is changed to Current. Note that this is based on XP2003 which will trigger an event with a data validation dropdown. It will also copy the data more than once if the dropdown is reactivated.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 6 And Target.Cells.Count = 1 Then
  Target.EntireRow.Copy
  Sheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 End If
 Application.CutCopyMode = False
End Sub

For the deletion aspect, is there a unique key that can be used to identify the particular entry on sheet4. I'm assuming that any deletion will delete the entire row on sheet4 (as distinct from clearing the row, but leaving a blank row) so the current row number cannot be used to identify the data.


Tony