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]

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

Glad to see your helping out here. I have no answer why this would not work

 

[dmt32]

Glad to see your helping out here. I have no answer why this would not work

Have not tried your solution but looks like it should do what OP wants.
My initial thoughts were OP may have had an unhandled error leaving EnableEvents disabled which may explain why code stopped working. Personally, I would include error handling in code when Events are disabled.

Anyway, we are all here to help & hopefully OP has now figure out the problem.

Dave

 

[holyrollyp]

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

Hi Dave,
thanks so much for the reply. Target column (col 5) is not a formula, but there is data validation in it and the only values are Pending & Completed. Is that an issue?
Also, can I add the code even when I am already in the middle of my list? Or do I need to create new excel file first, add the code and start populating the list by copying and pasting?

 

[holyrollyp]

Hello @My Aswer Is This / @dmt32
I think the code is working now. I removed the code, both of your codes. I created a blank file with all the headers and added the original code (thanks very much again). It worked very suddenly I started adding my list (manual copy and paste), my target column I change it to data validation and the codes still works. Looks like I no longer need to add the code from dmt32 (but thanks very much as well for the help there). My excel is working fine and dandy now. Maybe i was just doing a lot initially and my excel got cluttered.
You saved my day! Have a great weekend to you both.

Sincerest thanks,
Rolly

 

[holyrollyp]

Hello @My Aswer Is This
It's me again. I am sorry to come back. Regarding the original code above, looks like this works for myself only as a user. When another user access the file (stored on SharePoint Drive), excel file does not work when she changes the status to Completed. If I do the update myself, the code is still working. Do I need to update something in the code so it is not user specific?
Thanks in advance for the help.

 

[My Aswer Is This]

You said:
"When another user access the file (stored on SharePoint Drive), excel file does not work."

I know nothing about: "SharePoint Drive"
But my code has nothing to do with:
user specific?