auto move row when complete

[lilgreen]

Hello,

I want to move the entire row when I have 100% complete in "F" cell to new sheet deleting the old. Sheet name "ActionItem" move to sheet "Completed" I would also like to keep original formatting but it isn't necessary.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]request[/TD]
[TD]from[/TD]
[TD]priority[/TD]
[TD]assigned[/TD]
[TD]due[/TD]
[TD]status[/TD]
[TD]notes[/TD]
[TD]date updated[/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
Steven

 

[mumps]

Do you enter 100 in column F manually or is the 100 a result of a formula? Is column F formatted as a percent? If entered manually, do you want to move the row one at a time as 100 is entered into column F?

 

[lilgreen]

yes I enter 100 manually and the field is formatted to percentage. I have some default pull downs for that field: not started, 25, 50, 75, 100 pending, and on hold. Yes as soon as 100 is entered I want to move that entire row over.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "ActionItem" 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. The macro is triggered automatically whenever a value in column F is changed so you have to make sure that all the other columns are filled out first before selecting 100% from the drop down.

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

 

[lilgreen]

I get an error "Complie error: Ambiguous name detected: Worksheet_Change. I have another code running
Private Sub Worksheet_Change(ByVal Target As Range)


Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Target
Application.EnableEvents = False
Cells(cell.Row, "H") = Now()
Application.EnableEvents = True
Next cell

Application.ScreenUpdating = True

End Sub

Does this matter? Not good with code so please excuse me. I am learning.

 

[lilgreen]

Sorry it works I made a mistake thank you so much you rock

 

[mumps]

You are very welcome.