Macro to cut/paste entire rows from one tab to Archive tab based on criteria in drop down

Hello Excellers,

I've been looking/adapting code for quite a while for my problem and I can't quite find a code that will help me so I hope you guys and gals can.
I am looking to move entire rows of data based on criteria in column K (either 'Closed' OR 'Cancelled') from one sheet (ChangeRegister) to another sheet (Archive), then to delete the empty rows on the source spreadsheet. This is for use by other people including me so I don't want to put the code straight in the VBA editor, rather I will attached it a button to be pressed once a week.

Spreadsheet attached.

Any help would be really fantastic.

K

Will the button be in the workbook? When you click the button, where do you believe the code for that button is located?

Can you work with this?

Sub MoveRows()
    Dim lRws As Long, sh As Worksheet, x

    Set sh = Sheets("ChangeRegister")

    With sh
        lRws = .Cells(.Rows.Count, "K").End(xlUp).Row
        For x = lRws To 3 Step -1
            If .Cells(x, "K") = "Closed" Or .Cells(x, "K") = "Cancelled" Then
                .Range(.Cells(x, "A"), .Cells(x, "AT")).Copy
                Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "K").End(xlUp).Offset(1, -10).PasteSpecial xlPasteValues
                .Range(.Cells(x, "A"), .Cells(x, "AT")).Delete Shift:=xlUp
            End If
        Next x
    End With

End Sub

Originally Posted by kmort

Hi Dave, the button will be in the workbook and the code will be in a module?

A module is in the VBA editor