Nov 09 2022 06:53 AM
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!
Nov 09 2022 07:07 AM - edited Nov 09 2022 07:09 AM
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:
Nov 09 2022 11:47 AM
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).