Copy Row to New Sheet based on Conditional

Reena1024

New Member
Joined
Dec 3, 2004
Messages
32
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,767
Members
412,291
Latest member
marypolitan
Top