Macro to loop through drop down list and copy data to new sheet

I have a dashboard set up in which when you choose a doctor from the drop down menu (created with data validation list) in cell B2, a score is generated for the doctor in cell D20.

I would like to be able to automatically call up each doctor in the drop down menu and copy the provider name and their score into a new sheet. So, the columns in the new sheet would be labeled Provider (in cell A1) and Score (in cell B1). Each row would contain a provider from the drop down menu and their corresponding score.

Is there a way to do this?

Here is a link to a test sheet: https://www.dropbox.com/s/blo55eub9n...le_012814.xlsx

The "provider performance" tab is the dashboard that includes the drop down menu. I want to automatically loop through the drop down menu in B2 and copy and paste the provider ID from the drop down, along with the overall score in cell d20 into the provider id and overall score columns (a1 and b1) in the "provider data" tab.

The final output would have each of the 7 provider IDs and their scores populating the provider data tab from a2 to b8.

Thanks in advance!

Originally Posted by Mumps1

I'm not sure if I correctly interpreted what you want to do. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "Provider Performance" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell B2 and the ID and Score will be copied to the other sheet. You should format column A in the "Provider Data" sheet as a number with no decimal places.

Please Login or Register  to view this content.

Thanks, that is definitely getting closer then I did! I want it to copy and paste into the new tab, just as it is doing. However, I'd like to not have to select the provider from the drop down list. The actual data set will include thousands of providers, and I need it to loop through the drop down list automatically instead of having to manually select the next one in the list. Any thoughts on how to do that?

Try this code:

Please Login or Register  to view this content.

Originally Posted by Mumps1

Put this macro into a regular module and run it. Is this what you were looking for?

Please Login or Register  to view this content.

Yes, perfect, THANK YOU!