MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet

[Nicole Hannington]

I am trying to create a macro for a project management sheet in
excel. I have a column that has a drop down validation; when
"Cancel"
is selected, I would like the entire row to be moved to "Sheet2" in
the workbook. I want this to work for all of the projects (each
listed on a different row). I do not have that much experience with
macros, so detailed instructions will be grately appreciated

 

[Bernie Deitrick]

Nicole,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code in the window
that appears.

Change the 1 in this line

If Target.Column <> 1 Then Exit Sub

to the number of the "column that has a drop down validation" A=1, B=2, etc...

If Sheet2 is not named "Sheet2" then change it in this line:

Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)


HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "Cancel" Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub