When items are marked complete, move to another sheet

[milleniummanp7]

Hi Folks,

I know this question has been asked a million times (I keep finding them on Google), but I am a complete VBA novice and can never actually get it to work, so if someone can step me through what I'm trying to do, it would be much appreciated.

I'm keeping a log of tasks that are to be completed - when the task is marked complete in Col H, and the completed date entered into Column I, I want it to be automatically copied onto the "Completed Items" tab, and that row then removed from Active Items.

I've attached my sample data. Lines 2, 6, 8, 9 and 10 should all be moved into the Completed tab.

Ideally would also like to keep the formatting if possible, but it's not essential.

Can anyone help with this? Thanks!!

[Kevin#]

see if this is close to what you want
The trigger is when a date is put in the date column, but it will only trigger if column to left has "Yes" in it
If you put the date in first by mistake, delete it, then select "Yes" and then put the date in

Easy to modify if you want it trigerred on another basis ( eg when column H and column I are both complete and if column H = "yes")

Please Login or Register  to view this content.

[milleniummanp7]

Kevin#, thank you!! This is exactly what I needed!

Question, if I decided to remove the column for Complete Yes/No, and use only the date as the trigger, what do I need to change?

Also, rather than the window just saying "Trigger" is there a way I can change it to display a message like "Item moved to complete"? (just for aesthetics).

[Kevin#]

This assumes that you are deleting columnH, and that columnH now = date field

The macro is simplified
and triggered whenever there is a change in one of the date values

Please Login or Register  to view this content.

EDIT
Oh I forgot this bit:

Add this line immediately below the line beginning If Target.column...

Please Login or Register  to view this content.

or to be techically accurate (because it has not moved quite yet..)

instead add this line below Rows(Target.Row).EntireRow.Delete

Please Login or Register  to view this content.

[milleniummanp7]

This is exactly what I needed. Thanks a lot Kevin#!

[Kevin#]

Excellent
Don't forget to mark the thread as solved

thanks
K