Move a line to a new sheet/tab once a particular cell says keyword. Do i need macros?

[teenyjem]

I have several fields on the 'OPEN' tab. Each MUST be populated, and the S column has a dropdown box saying Complete, which must say this before moving the whole row to the 'CLOSED' tab.

Similiarly, if someone wants to reopen the case, there are several other options apart from Closed, ie open, quarantine etc, and if the status is reopened in any way and it doesnt say the word closed, i want it to move back to the top row of the 'OPEN' tab sheet...

I want the line to move if the dropdown at the end of the sheet (COLUMN S) says complete, and if this changes, it goes back to the first sheet?

Can someone please tell me what to do?

[JBeaucaire]

To answer the question posed in the title "Do I Need Macros?", yes. Formulas can display results in the cell in which they are placed, formulas cannot make physical changes to the worksheet/workbook. Macros are required for that.


1) I've put some minor suggested corrections in the formula of OPEN sheet, columns R and T
2) I've changed the named range formulas on the LIST sheet, now you can freely add items to the lists in any column and the named range will dynamically expand itself for you without you needing to do anything special to insert. Just add the items in the next empty cell in any column, sort if you wish.
3) I've added a macro into the OPEN sheet tab module and the CLOSED sheet tab module. Right-click the sheet tab and select VIEW CODE to see/edit them.

OPEN sheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Not Intersect(Target, Range("U:U")) Is Nothing Then
    Application.EnableEvents = False
    For Each cell In Intersect(Target, Range("U:U"))
        If cell.Value = "Closed" Then
            Rows(cell.Row).Copy Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Rows(cell.Row).Delete xlShiftUp
        End If
    Next cell
    Application.EnableEvents = True
End If

End Sub

CLOSED sheet module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Not Intersect(Target, Range("U:U")) Is Nothing Then
    Application.EnableEvents = False
    For Each cell In Intersect(Target, Range("U:U"))
        If cell.Value <> "Closed" Then
            Rows(cell.Row).Copy
            Sheets("Open").Rows(2).Insert xlShiftDown
            Rows(cell.Row).Delete xlShiftUp
        End If
    Next cell
    Application.EnableEvents = True
End If

End Sub

It all seems to work as you described.

[teenyjem]

Hey there, sorry to bother you... You really helped me with the last issue i had... can you please help me do something else?
i tried to delete the Q & S columns (on the amended version of the sheet you made) but it messed up my macros. i tried to amend them but i messed it up. i asked on the forum but i am a little confused with what he is doing... could you please explain to me how to amend it? as it relies on the U column to say closed to work, but when i have deleted the two rows, it will now need to say S column needs to say closed...

I am very sorry to bother you...

[teenyjem]

You are a star! Thank you soooo much!

[JBeaucaire]

I can't see your edited code unless you post it.

[teenyjem]

Basically just where it says U:U, i changed it to S:S, but it didnt work.
I thought that, as the cells would be moving back two places from U (when i delete the cells i mentioned), the other place that would be having the 'closed/open' selection would now be the S column. But it wouldnt work.... God, im confused?!

[JBeaucaire]

If the changes are now being made in column S and you changed the code to watch S:S instead of U:U, that's what it will do.

I can't comment on your non-working sheet nor code until you post them.