How can I use VBA to create a button that will cut a row from one sheet and paste it in another when clicked?

[medwardnfriends]

I need to write a VBA code that will create a button or buttons that will copy and remove (cut) an entire row from one tab titled "2011 Projects", and paste the row in the next empty row in another tab titled "2011 Completed Projects" once the button is clicked. I have not been able to figure this out yet. please help!

 

[mikerickson]

There are two parts to this. 1)the button and 2) the code that it will call.

For the code, something like this should work.

Sub MoveRow()
    With ActiveCell
        .EntireRow.Copy Destination:=Sheets("2011 Completed Projects").Cells(Rows.Count, 1).End(xlup).Offset(1, 0).EntireRow
        .EntireRow.Delete shift:=xlup
    End With
Exit Sub

Ideally, creating the button should be done at Design time. (i.e. now, not via VBA triggered by the user). If you don't want it visible at all times, toggling the .Visible property is better than creating new command buttons.

 

[VoG]

Try with a button from the Controls toolbar (ActiveX control)

Private Sub CommandButton1_Click()
ActiveCell.EntireRow.Cut Destination:=Sheets("2011 Completed Projects").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[JackDanIce]

Welcome to the board

You don't say how you select or define the row that needs to be cut and moved. I'm going to assume you click onto a value in that row and then you can run the following macro (you can assign this to a button after):

Sub MoveMe ()

Dim msg as String
Dim i as Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

If MsgBox(msg, vbYesNo) = vbNo And ActiveSheet.Name <> "Sheet1" Then
    MsgBox ("The correct sheet has not been selected or you clicked No. Macro stopping")
    Exit Sub
End If

ActiveCell.EntireRow.Cut
With Sheets("Sheet2")
    .Select
    i = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & i).Select
    .Paste
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[medwardnfriends]

Thank you for your help. The macro worked as I hoped. Now I'm wondering if there is a way to design the sheet "2011 Projects" to include a button where once a row is selected, and the button is clicked, the macro will be activated and move the row to the other sheet?

 

[VoG]

Well that is essentially the reverse. You just need to change the name of the destination sheet in the code.

 

[medwardnfriends]

Thanks alot, I got it to work exactly how I wanted.