Automatically move rows to another sheet and back based on value

[Kgreen214]

Hi,

Currently I am tracking Agenda Items in an excel spreadsheet. I have created the spread sheet with drop down menu’s (See attached Image). Both the Active sheet and the Completed look the same. I’m looking to move a row from my “Active” sheet to the “Completed” sheet once the status column has been updated to Complete. I would also like for that row to be deleted so that there isn’t blank rows on the sheet.

Subsequently I would also like to have it set up that if the status has been updated accidently it can be moved back to the Active sheet just by changing the status back to either “Not Started, In Progress, On Hold or Overdue”

Can someone please assist me with this? Thanks!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Active" 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.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Complete" Then
        With Sheets("Completed")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Right click the tab name for your "Completed" sheet and click 'View Code'. Paste the macro below into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Not Started" Then
        With Sheets("Active")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

In each sheet, make a selection in column E.

 

[Kgreen214]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Active" 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.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Complete" Then
        With Sheets("Completed")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Right click the tab name for your "Completed" sheet and click 'View Code'. Paste the macro below into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Not Started" Then
        With Sheets("Active")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

In each sheet, make a selection in column E.

Hi,

Unfortunately nothing is happening. I pasted the individual codes into the specific sheets and tried testing it and nothing is happening. I enabled Macro's and still nothing. Any idea's??

 

[mumps]

I tested the macros in a dummy workbook and they worked as you requested. Are you sure that the macros were placed in the worksheet code modules and not in regular modules?

 

[Kgreen214]

I tested the macros in a dummy workbook and they worked as you requested. Are you sure that the macros were placed in the worksheet code modules and not in regular modules?

Hi,

So I just closed out of the file and reopened the file and tested the macro again and it worked. Thank you so much for your help.

 

[mumps]

You are very welcome.

 

[LAMB]

I tried to use the code and didn't work, can I get some guidance. Trying to get the row to automatically go to "Closed" sheet when job status goes to completed.

 

[mumps]

Make sure that you place the macro in the "Data" worksheet code module, not in a regular module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Completed" Then
        With Sheets("Closed")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[LAMB]

I am definitely challenged, I added the code, following the instructions above. Dosent move the line to closed tab.

 

[LAMB]

I found my error, works perfectly. Thank you for the help.