Move/Copy rows dependant a cell's value 1

[shardian]

Hi all, hope some one can help me.

I've been asked to create a spread sheet that lists tasks/jobs for a department for each month.

Each month will have its own worksheet and the details for each task/job will be entered. As each is completed, the last cell in the row (column G)for that particular task will be changed to "completed." That cell is a drop down box and the other option is "Follow Up."

At the end of the month, I want to have a button that is attached to a macro so I can click it and move/copy all the rows that have the end cell value "follow up" to the next month's sheet.

I've had a look with google and a few possibilities seem promising but I've had no success with them as yet.

If anyone could help me it'd be much appreciated.

thanks

-matt

 

[Gavona]

If you kept all the info in a single sheet then you could simply use an advanced filter to show rows which met either of your two conditions:
* DateDue = CurrentMonth
OR
* Status = "Follow Up"

(Consider changing from Status to Date Completed. That way you will end up with a database that can tell you more about performance.)

For macro related questions then please post in forum707. However I would simply create a macro that managed the above. Probably triggered by a change in a cell at the top of the sheet that indicated "current month". That macro would also change the status for all overdue jobs to "Followup".

If you insist on separate sheets then advanced filter could still be used. It would extract the required data (overdue tasks) to a temporary location. You would then need a macro to copy it from there to the relevant month sheet, below(?) any normal tasks already on the sheet.
You would also need to deal with the issue that the source and destination sheets differ each month. Is there a risk that the macro will be run twice? Or data changed on an historic month sheet after the macro has run? Assess the risk and impact of such things and you can build processes to manage them.


Gavin

 

[shardian]

Thanks for your help.

I figured out a different way. I Setup an autofilter to filter out any 'completed' tasks and then made a macro to highlight the selection and copy and paste it to the next sheet.

Then I tried putting the macro's code under a command button, but it had problems, so I shifted the macro's code to a module and referenced the button on each sheet to the macro in the module.

End result was it worked. Thanks for you assistance.