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

 

[Jim Thomlinson]

Here is how I would go about it... You need a way to determine if Cancel was
choosen in the specific cells where doing so requires a copy operation. To
that end I would create locally defined named ranges on the cells (on each
sheet) where your validation exists (I used the word This as my names
ranges). At that point can you can use sheet change code in ThisWorkbook do
determine that the change requires a copy with code similar to this...

(Right click the XL icon in the upper left cornter of the XL window and
select View Code. Paste the Following)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Sh.Range("This"), Target) Is Nothing And _
Target.Value = "Cancel" Then
Target.EntireColumn.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

There is more to the code than this but it is a start...