Macro to extract data from another sheet based on specific criteria, then clear data after update. Repeat.

[sfetaih]

Hello,

I am not very good at VBA coding and need help putting together a macro that will allow me to do the following:

1. Extract milestones from another worksheet (titled "Workplan") based on a column titled Upcoming Milestones (column R) that is a drop-down selection (Yes or No). If yes, I'd like the actual milestone (column E) to be extracted to a worksheet (titled "Status Update"), with its corresponding Due Date (column M) and Status (column O) from the Workplan tab. It should populate columns F, G, H respectively in the Status Update tab.

2. Once the user updates his/her Workplan tab the following week, and indicates new Upcoming Milestones (Yes), should clear and re-populate new Upcoming milestones with its corresponding Due Date and Status.

Would appreciate your help on this! Thanks so much.
Best,
SF

 

[mumps]

Would the new Upcoming Milestones replace any previous milestones in the "Status Update" sheet or would the new ones simply be added at the bottom? Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[sfetaih]

Thank you very much for your prompt response! I really appreciate it. Since I'm using my work computer, I can't use any of the free sites like dropbox to upload the screenshots / Excel file. Is there another way to upload the Excel file or screenshots? To answer your question, the new Upcoming Milestones will replace previous ones, as it's a fluid file that will be updated weekly by the user. For instance, once a Milestone is completed, it should be replaced by other upcoming milestones in the Status tab, based on the Yes or No dropdown in the Workplan. Also, I forgot to mention that there are two separate workplans, so the macro will need to consolidate all milestones with a "Yes" in both tabs, into the Status Update tab. Is that doable? Really appreciate your help!

Thanks again for your help.

 

[mumps]

Since there are two separate workplans, does that mean that a user will have two separate entries in the "Status Update" sheet? In order to do what you want, there has to be some way to link the data in each workplan to the "Status Update" sheet. This means that there has to be a unique identifier for each user such as an ID number. If the user will have two separate entries in the "Status Update" sheet, there also needs to be some way of distinguishing between the two workplans on the "Status Update" sheet, for example, WP1 and WP2. Do you have a unique identifier of some sort for each user? If not, can one be added? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html A screen shot of both the "Status Update" sheet and workplan sheet would be very useful.