macro to select each item in data validation list

[Olivia Wong]

Good morning everybody,

I have a data validation drop down list in cell A4 (I name this cell as IState).
I name items list for data validation drop down list as IStateMarket40 States)

Alabama
Arizona
Arkansas
California
Colorado
Idaho
Illinois
Indiana
Iowa
Kansas
....

How do you create a macro to loop through (automatically select state one after the other) the drop down list? I ultimately want to create a copy of the worksheet within the workbook without formula based on changing of each item in the data validation drop down list.


1) select first item in drop down list (range IState) (the formula will updated value based on the selection in range IState)
2) create a copy of worksheet without formula right before sheet 7,
3) Rename the new worksheet based on the text in Cell B1. For example: rename worksheet "Report (2)" to "AK" which "AK" shows in Cell B1 when IState is "Arkansas" (I use vlookup formula in Cell B1 to automatically change accordingly based on the selection in IState)
4) select next item in drop down list
5) repeat steps 2 & 4 until done with 40 states

Thank you so much for your help!

Olivia

[stnkynts]

A validation list needs to have data to cross reference for its values somewhere. What is the range that the 40 states are listed.

[Olivia Wong]

To Stnkynts,

Thank you so much for quick response.

The range for the 40 States are listed in another worksheet Ref M4:M44. I gave a name for this range as "IStateMarket"

Thanks!

Olivia

[AlphaFrog]

Try this. Check the red worksheet names and change to suit.

Please Login or Register  to view this content.

[stnkynts]

Give this a try. You are going to need to change the Main Sheet name in the code provided to match the sheet name with all the formulas. You didnt supply that information.

Please Login or Register  to view this content.

[Olivia Wong]

To Alphafrog,

Your code is working perfect. Thank you so much for your help. You make my date

Olivia

[Olivia Wong]

To Stnkynts,

Thank you no much for your help today. You guys are so smart, and I know I am able to learn a lot from your guys through my daily work. This is my first day on Excelforum, and I have great experience. It is my favorate internet place now.

Olivia