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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0

Forum statistics

Threads
1,212,150
Messages
6,106,257
Members
448,010
Latest member
wbarkwell

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
Back
Top