How to move an entire row to a new sheet when a status is changed in excel

[Ashbarber]

Hello, 

I would like to set up a worksheet where when a status is changed from open to closed the entire row is moved to a separate sheet in excel. Is this possible? How do I set it up? For reference I am working with excel 2013

Thanks!

[#2]

With 2013, you're going to need a macro to do this. Something to consider is keeping all data in one location in 1 sheet and then using filtering to get desired records. If data sets have the same type of information and the same fields, it's best the data is kept in the same sheet.

You could also use Custom Views to switch quickly between a set of views:

Create, apply, or delete a custom view (microsoft.com)

[#3]

@Ashbarber 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngBereich As Range

Set rngBereich = Range("B2:B10000")
Application.EnableEvents = False

If Target.Cells.Count > 1 Then GoTo done

If Not Application.Intersect(Target, rngBereich) Is Nothing Then

If Target.Value = "closed" Then

Sheets("A").Rows(Target.Row).EntireRow.Copy _
Destination:=Sheets("B").Range("A" & Rows.Count).End(xlUp).Offset(1)

Else

End If
End If

done:
Application.EnableEvents = True
Exit Sub

End Sub

An alternative could be this code. In the attached file you can change "open" to "closed" in range B2:B10000 in sheet "A" and the whole column is copied to the next empty row in sheet "B" (starting in row 2).