Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet
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 :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Selecting "Cancel" from a drop down to copy and paste entir
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... -- HTH... Jim Thomlinson "Nicole Hannington" wrote: 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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
[newbie] Macro for "copy and paste" | Excel Discussion (Misc queries) | |||
How do I cancel the "Send this Sheet" option? | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Macro to find copy "header" and paste | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) |