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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,489
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top