Copy, Move and Delete Row, based on Cell Value

[Domn8r]

I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list.
I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1.
The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
Hope someone can help me with this
Thanks!!!

 

[Domn8r]

OK, now I`m getting `Runtime Error 9; Subscript out of range`

I need a beer!!!

 

[Legacy 68668]

Check the sheet name.

I've copied it from your post.

Sheet 1 is titled `Current Work Orders` and Sheet 2 is Àrchived`.

Àrchived

 

[Domn8r]

AHA!!!! Well spotted; I dunno how I slipped that little accent in there!

OK; the command button now moves the CLOSED work orders to Sheet 2 - Archived.

However... I only get one record on sheet 2, how can I send records to sheet 2 on a new line for each record.
Also, once the closed order is copied to sheet 2, the status drop list on sheet 1 is no longer available on the row that has just been cleared.

 

[Legacy 68668]

However... I only get one record on sheet 2, how can I send records to sheet 2 on a new line for each record.
Also, once the closed order is copied to sheet 2, the status drop list on sheet 1 is no longer available on the row that has just been cleared.

It is not "Clearing", but "Cut", removing the row.
So the rows shifted up comes after the last row in E6:E100 will not have Data Validation list.
How about this then?

Private Sub Archive_Click()
'Archive Closed Work Orders to Sheet 2 - 'Archive'
Dim rng As Range, x, r As Range
Dim Answers As Variant, myValidation As Range
Dim Colors As Variant
Colors = [{15, 38, 44, 42, 20, 36}]
Answers = Array("SUSPENDED", _
"COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _
"SCHEDULED", "READY")
Set rng = Range("E6:E100")
again:
For Each r In rng
    If r.Value = "CLOSED" Then
        r.EntireRow.Cut Sheets("Àrchived").Cells(Rows.Count, 1).End(xlUp)(2)
        GoTo again
    Else
        x = Application.Match(r.Value, Ansers, 0)
        If IsNumeric(x) Then r.EntireRow.Interior.ColorIndex = Colors(x)
    End If
Next
On Error Resume Next
Set myValidation = rng.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not myValidation Is Nothing Then
    myValidation.Areas(1).Cells(1).Copy
    rng.PasteSpecial xlPasteValidation
End If
Application.CutCopyMode = False
End Sub

 

[Domn8r]

FANTASTIC! Thanks very much!!!

All I have to work on now is getting the CLOSED orders on a new line

 

[Legacy 68668]

Isn't it adding to a new line in other sheet ?
try change

        r.EntireRow.Cut Sheets("Àrchived").Cells(Rows.Count, 1).End(xlUp)(2)

to

        r.EntireRow.Cut Sheets("Àrchived").Cells.SpecialCells(11) _
       .Offset(1).EntireRow.Range("a1")

 

[Domn8r]

WoHoooo!!!!

You made my weekend!!!!!!! Thank-you so much!

 

[Legacy 68668]

My pleasure and welcome to the board.