Move rows from pending to completed tab

[holyrollyp]

Hello MrExcel Gurus/Friends,

I need help please. In the attached file, I wanted to create a macro that fires automatically whenever the Status column has a value of "Completed".
For all completed (status), I wanted to move that row from Pending tab to Completed tab. My Completed tab has some previously completed already, and the newly completed rows from pending tab, I need to keep adding into the list.
So basically, I want pending to have pending only, and keep adding all completed to the Completed tab.

Can you please help me. Thanks so much in advance for any help or recommendation.

Thanks,
Rolly

 

[My Aswer Is This]

This is a sheet change again script:
Right click on sheet named Pending.
And paste in this code.

I f you enter the value "Completed" into any row column 5 of sheet named "Pending".
The script will copy this row to a sheet named "Completed"
And deleted the row from sheet named "Pending"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim answer As String
Dim Lastrow As Long
Dim ans As Long
answer = Target.Value
If answer = "Completed" Then
ans = Target.Row

Lastrow = Sheets("Pending").Cells(Rows.Count, "E").End(xlUp).Row + 1
Lastrow2 = Sheets("Completed").Cells(Rows.Count, "E").End(xlUp).Row + 1

Sheets("Pending").Rows(ans).Copy Sheets("Completed").Cells(Lastrow2, 1)
Sheets("Pending").Rows(ans).Delete
Application.ScreenUpdating = True

End If
End If
Application.EnableEvents = True

End Sub

 

[holyrollyp]

Thanks very much, @My Aswer Is This.
I did right click on the Pending tab and pasted the code as is. I clicked Saved in the VB code editor and then Close the window. How can I call this Change sub now?
I went to Developer > Macros, and I don't see the Macro I just saved. I went to Visual Basic and I can actually see the code I saved, I just can't figure how to run it (automatically each time there is completed value). I already turned the macro enable on. My file now says .XLSM
How do I run this code now? Can you please assist once more?

 

[My Aswer Is This]

This macro will not be seen in a Module.
The script runs as I mentioned in my last Post:
Quote:
I f you enter the value "Completed" into any row column 5 of sheet named "Pending".

 

[holyrollyp]

This macro will not be seen in a Module.
The script runs as I mentioned in my last Post:
Quote:
I f you enter the value "Completed" into any row column 5 of sheet named "Pending".

The script is not running, unfortunately. am i not going to trigger it?

 

[My Aswer Is This]

The script works for me.
Now I see you have an image here of all your settings. So not sure what all that is about.
Your workbook must be saved as macro enabled
And from your image of settings. You do not have macro enabled.
I have never got into trust settings

 

[holyrollyp]

Hello again. I have my macro enabled. The code ran for a bit and then suddenly it no longer works and don't know what's causing it to stop to work.

 

[dmt32]

Hello again. I have my macro enabled. The code ran for a bit and then suddenly it no longer works and don't know what's causing it to stop to work.

Hi
Try running this code in a STANDARD module & see if resolves your issue.

Sub Reset()
    Application.EnableEvents = True
End Sub

Dave

 

[holyrollyp]

Hi
Try running this code in a STANDARD module & see if resolves your issue.

Sub Reset()
    Application.EnableEvents = True
End Sub

Dave

Thanks, Dave. I did as you instructed, but the code won't work still. I confirm that macro has been enabled already. I had the original code added under the Pending tab, then your code into the standard module.

 

[dmt32]

Hi
code you have is event code & should, providing events have not been disabled, automatically run when you make a direct change (not by formula) to any cell in target range i.e Column 5 in the Pending worksheet.

If still not resolved post back & will look further.

Dave