Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to move completed action item
I'm trying to move completed action items from one worksheet to another via a
command button on the excel page. The macro I have is... Sub MoveCompletedTasks() ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Sheets("Closed Action Items").Select ActiveCell.Rows("1:1").EntireRow.Select Rows("4:4").Select Sheets("Action Items").Select Selection.Cut Sheets("Closed Action Items").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Action Items").Select ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Selection.EntireRow.Delete End Sub I get "Error 400" after Range("A1").End(xlDown).Offset(1, 0).Select Any suggestions to move a completed task to another page is appreciated. I'd like to avoid filtering...I'm trying to make it as 'manual' free as possible. Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to move completed action item
You have so many extraneous and non-productive 'select" lines I'm not sure but
maybe this will work, Sub Test() Sheets("Action Items").Select ActiveSheet.Range(ActiveCell, Cells(ActiveCell.Row, _ Columns.Count).End(xlToLeft)).Cut _ Destination:=Sheets("Closed Action Items") _ .Range("A1").End(xlDown).Offset(1, 0) End Sub On Mon, 4 Feb 2008 12:14:02 -0800, JFREE223 wrote: I'm trying to move completed action items from one worksheet to another via a command button on the excel page. The macro I have is... Sub MoveCompletedTasks() ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Sheets("Closed Action Items").Select ActiveCell.Rows("1:1").EntireRow.Select Rows("4:4").Select Sheets("Action Items").Select Selection.Cut Sheets("Closed Action Items").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Action Items").Select ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Selection.EntireRow.Delete End Sub I get "Error 400" after Range("A1").End(xlDown).Offset(1, 0).Select Any suggestions to move a completed task to another page is appreciated. I'd like to avoid filtering...I'm trying to make it as 'manual' free as possible. Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to move completed action item
hi
you have a number of errors and redundencies in your code. try something like this instead ActiveCell.EntireRow.Copy Sheets("Closed Action Items").Activate ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteAll Sheets("Action Items").Activate ActiveCell.EntireRow.Delete shift:=xlUp regards FSt1 "JFREE223" wrote: I'm trying to move completed action items from one worksheet to another via a command button on the excel page. The macro I have is... Sub MoveCompletedTasks() ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Sheets("Closed Action Items").Select ActiveCell.Rows("1:1").EntireRow.Select Rows("4:4").Select Sheets("Action Items").Select Selection.Cut Sheets("Closed Action Items").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Action Items").Select ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Selection.EntireRow.Delete End Sub I get "Error 400" after Range("A1").End(xlDown).Offset(1, 0).Select Any suggestions to move a completed task to another page is appreciated. I'd like to avoid filtering...I'm trying to make it as 'manual' free as possible. Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to move completed action item
One more...
Option Explicit Sub MoveCompletedTasks2() Dim RngToCopy As Range Dim DestCell As Range Set RngToCopy = ActiveCell.EntireRow With Worksheets("Closed Action Items") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With RngToCopy.Copy _ Destination:=DestCell RngToCopy.Delete End Sub JFREE223 wrote: I'm trying to move completed action items from one worksheet to another via a command button on the excel page. The macro I have is... Sub MoveCompletedTasks() ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Sheets("Closed Action Items").Select ActiveCell.Rows("1:1").EntireRow.Select Rows("4:4").Select Sheets("Action Items").Select Selection.Cut Sheets("Closed Action Items").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Action Items").Select ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Selection.EntireRow.Delete End Sub I get "Error 400" after Range("A1").End(xlDown).Offset(1, 0).Select Any suggestions to move a completed task to another page is appreciated. I'd like to avoid filtering...I'm trying to make it as 'manual' free as possible. Thanks, -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command button to move completed action item
Works Perfectly. Thanks to the both of you for your suggestions.
"JFREE223" wrote: I'm trying to move completed action items from one worksheet to another via a command button on the excel page. The macro I have is... Sub MoveCompletedTasks() ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Sheets("Closed Action Items").Select ActiveCell.Rows("1:1").EntireRow.Select Rows("4:4").Select Sheets("Action Items").Select Selection.Cut Sheets("Closed Action Items").Select Range("A1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste Sheets("Action Items").Select ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select Selection.EntireRow.Delete End Sub I get "Error 400" after Range("A1").End(xlDown).Offset(1, 0).Select Any suggestions to move a completed task to another page is appreciated. I'd like to avoid filtering...I'm trying to make it as 'manual' free as possible. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Undo delete button action | Excel Worksheet Functions | |||
Action Item Worksheet Creation | Excel Worksheet Functions | |||
Trigger Action when Standard Menu Item Selected? | Excel Discussion (Misc queries) | |||
How do I create an action button in excel? | Excel Discussion (Misc queries) | |||
command button in excel will move when print. | Excel Discussion (Misc queries) |