Macro – If Equal to Variable, Copy and move to Next

I have been unable to locate a solution to the following:

A workbook with two spreadsheets titled “Sheet 1” and “Sheet 2”. In “Sheet 1”, I have a table that lists employee names, whether or not they are used on the project (Posed with a question and answered with a drop down list of “Yes” or “No”), and charge rates in columns down. I need to transfer the names of employees that are used on the project (“Yes”) to “Sheet 2” in a row format (B3:L3 for example). I need a macro that will check the first value in the specified range in “Sheet 1” of “Yes” and “No”, add the employee name to a new list if the answer is “Yes”, ignore if the answer is “no” and go through the entire range until it reaches the end.

Any help would be much appreciated.

Example portion of “Sheet 1”


Column A Column B Column C

Name Used on Project? Rate

Alexander Yes $213.00
MacNeill No $213.00
Kirkland No $223.00
Karpathy Yes $213.00

Thank you both for your responses. I should have been more clear in my first request. I have attached a spreadsheet now in hopes that it will help better explain what I am trying to achieve.

In the tab called “2011 Rates”, in Column E there is a drop down list that the user can choose “Yes”, “No”, or “N/A”. If “Yes” is chosen I would like the “Project Title” (Column B) to be shown in Row 3 (C3:….) of the tab called “Workscope_BY_CTR”. Currently there is a drop down list in Row 3 of “Workscope_By_CTR”, I am trying to eliminate the step of having to use the drop down list. I would like to put a button in the “Workscope_By_CTR” tab that will populate the entire Row 3 with unique “Project Titles” that “Yes” has been chose for in the “2011 Rates” sheet. I would also like to avoid any issues that may arise if the user pushes the button twice.

Thanks again!

Thanks for the response again MarvinP. Is it possible to have you paste the code in a post? I think something may be happening in the conversion process because I’m getting some error messages about referencing external formulas and the code is appearing in a “Sheet 18”.

Actually, I figured it out. Thank you so much!!!! This will make things a lot easier!

MarvinP, I really appreciate the help. I have one more question about a macro to complete the spreadsheet. I know that I should be able to figure out how to make a similar code as the previous one that you sent me, but I seem to be having trouble. Similar to the other code, in the same workbook there is a “Summary Tab” that summarizes the amount of hours allocated for each employee, I need the code to extract the name of the employee that has hours in the respective CTR sheets to the sheet titled “CTR-01” in the section titled “Personnel”. The “CTR-01” sheet currently has a drop down list, again I’m trying to eliminate a step in which the user would have to manually chose the name of the “Personnel.” The original spreadsheet has 20 CTR sheets, but I assume that once the code is written it should be easy to include in all the CTR sheets. I have attached another spreadsheet to help my inquiry make more sense.

MarvinP = I’m wondering if it would be better to have two Macros, a separate one in the “Summary” tab. Currently the macro is listing all the Employee Names in CTR-01, even the ones that don’t actually have hours. So I was wondering about having a macro that went only off of the summary tab, for example if the value in column 3 of the summary tab is greater than 0, then it lists only those names in the CTR-01 tab.

Thanks again for the help!