Macro to cycle through all items in a drop-down list

[qskmtk]

Hello ~

I am trying to figure out if a macro is possible at all to run through all items in a drop down list.

Here is my example:
- Drop-down list in cell B3, which selects 5 possible countries in a range defined as "country";
- When I change my selection in the drop-down list, revenue (just for example) is calculated for the country selected;
- Every time I change the country, I want to copy / paste the revenue figures as values into a different sheet (so that at the end of the day, I will have a separate sheet that has all five countries' revenue figures pasted as values);

How do I make this (just the first bullet point above) happen using macro, if possible at all.
Thanks!!!

 

[Smitty]

Welcome to the Board!

Have you thought of a Pivot Table? If you put country in the filter field, you can use the Show Report Filter Pages option, which will create a new pivot table worksheet for each item in the list. The nice thing about this is that as you add new data all you do is Refresh All to update the pt's.

 

[qskmtk]

Hi Smitty,

Thanks and thanks for the response.
Let me know if I am not understanding your suggestion correctly but I am not sure if that will work for my purposes. I simplified my situation just for illustration purposes, but the country drop-down list is my country toggle in the input sheet and my model does all kinds of calculations for each country based on country specific data / assumptions.

I can think of ways to go around it actually but it's just a pain in the *** and I am really curious if there is a much smarter way to get the drop-down list work in macro.

Thanks,

 

[Smitty]

OK,

This assumes that your list of countries is in A1:A5 on the sheet that you want copied. Note that it's not going to check if the sheets already exist first, so it will blow up if they do. But that can be added if you need it.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Range("A2:A6")<br>        Range("B3").Value = c.Value<br>        ActiveSheet.Copy After:=Sheets(Sheets.Count)<br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            .Name = c.Value<br>            .Cells.Copy<br>            .Cells.PasteSpecial xlPasteValues<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>