CUT/PASTE Row to Different Sheet Based on Cell Value

[wampuswingding]

I know. I know. I've seen this on here a million times, but I've tried several codes, altered them with my info, and they just don't work.

I have a project log where multiple users update their progress on certain tasks. When a task is completed I'd like the row with that task cut and pasted into a 'Completed' sheet. So when the cell value of column F says 100% the row would be moved to the other sheet to keep the Task Log uncluttered.

Is there a simple way to do this?

 

[mumps]

Does the user enter 100 in column F manually when the task is completed? Are the cells in column F formatted as 'Percentage'?

 

[wampuswingding]

They enter 100 and it's formatted as a percentage.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your 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. Enter 100 in any cell in column F and exit the cell. The macro is triggered by a change in a cell in column F so the 100 must be the last value entered after the rest of the data has been entered on the task row.

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

 

[wampuswingding]

That was glorious. Thank you so much!

 

[mumps]

You are very welcome.

 

[wampuswingding]

Um... something seems to be wrong. When I inserted a new row for an employee, I received an error and a suggestion to debug. Now the macro doesn't work anymore. Ideas?

 

[mumps]

When I tried inserting a new row, the macro did not return an error. How are you inserting the new row? Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo errHandler
    If Target = 1 Then
        Target.EntireRow.Copy Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
errHandler:
    Application.EnableEvents = True
End Sub

 

[wampuswingding]

I just right-clicked on a row and chose 'Insert.'

The new code isn't doing anything either. Did I mess it up?

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.