Excel Programming Help - Move Rows

[cptnmerika]

Good day, the issue I am needing help with is the following.

I have a workbook with 5 sheets in it (new, delayed, in process, completed, and cancelled). In each of those sheets is a column named "Status" (all sheets are column F). What I want to do, is have all the sheets do something the same....when the status is changed from "new" to "delayed", I want the row to move from the "new" sheet to the "delayed" sheet. When column F is changed from "XXXXX" to "XXXXX" I want it to be automatically moved from the sheet is is on, to the sheet that corresponds with its status.

Can anyone help me with this?

[cptnmerika]

To add on to this, I do not want a macro I have to run, I want the workbook to do this automatically when the status is changed.

[Mumps1]

Is the status changed manually or is it the result of a formula?

[cptnmerika]

Is the status changed manually or is it the result of a formula?

Manually using a list

[Mumps1]

I know that you said that you "do not want a macro I have to run". This macro will run automatically when you change the status in column F of the "new" sheet. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "new" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change the status in column F and that row will be copied to the appropriate sheet and deleted from the "new' sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Select Case Target.Value
    Case "delayed", "in process", "completed", "cancelled"
        Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The macro uses all lower case letters for your sheet names as you described in your original post. If this is not the case, change the sheet names in the code to match yours.

[cptnmerika]

Mumps1:

Thank you for your quick response; however, when I follow your instructions, it still does not work. I pasted the code, and when I go to row F and click the drop down to select "XXX" (status), it does not copy or delete.

[Mumps1]

Can you attach a copy of your file? De-sensitize it if necessary. It is always easiest to test possible solutions on the actual file.

[cptnmerika]

It is attached now.

[Mumps1]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Select Case Target.Value
    Case "Delayed", "In Process", "Completed", "Cancelled"
        LastRow = Sheets(Target.Value).Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & LastRow + 1)
        Target.EntireRow.Delete
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Your drop down list has the sheet name "In Progress" while the actual sheet name is "In Process". In the macro, I have used "In Process" as the sheet name. If you want to use "In Process" as the name, you will have to change your drop down list to match.

[cptnmerika]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Select Case Target.Value
    Case "Delayed", "In Process", "Completed", "Cancelled"
        LastRow = Sheets(Target.Value).Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & LastRow + 1)
        Target.EntireRow.Delete
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Your drop down list has the sheet name "In Progress" while the actual sheet name is "In Process". In the macro, I have used "In Process" as the sheet name. If you want to use "In Process" as the name, you will have to change your drop down list to match.

Sorry, good catch. That worked...but I do have an addition to that. Is there a way to have the rows move in any sheet once the status is changed to the corresponding sheet? For instance, if it is is new > status changed to in progress > row moves to in progress.....once the task is complete > move status to completed > row automatically moves to completed sheet.

[cptnmerika]

I hate to be the bearer of bad news, but it was working initially, but for some reason it is not working now.

[Mumps1]

Try the attached file. I have moved the Worksheet_Change macro from the "New" Worksheet code module to the code module for ThisWorkbook. In this way, the macro will be triggered on all the sheets instead of just the "New" sheet. Also, I have added error tracking to the macro. The reason for this is that if the macro errors out before it can finish running, the line "Application.EnableEvents = True" (it was set to "False" at the beginning of the macro) does not get executed which means that macros are disabled and the next time you make a selection in the drop down, nothing happens. Now if the macro errors out, it will go to "Application.EnableEvents = True" which re-enables the macros for the next time you make a selection. I hope this makes sense.