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

sfetaih

New Member
Joined
Apr 4, 2018
Messages
2
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

New Member
Joined
Apr 4, 2018
Messages
2
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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.
 

Forum statistics

Threads
1,148,108
Messages
5,744,874
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top