macro to select each item in data validation list

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

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

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

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

Sub IState()
'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
    
    Dim wsReport As Worksheet
    Dim rngState As Range
    
    Set wsReport = Worksheets("Report")             'source worksheet
    
    Application.ScreenUpdating = False
    For Each rngState In Sheets("Other").Range("IStateMarket")      'loop through list
        wsReport.Range("IState").Value = rngState   'select state
        wsReport.Copy Before:=Sheets("Sheet7")      'copy sheet
        With ActiveSheet
            .UsedRange.Value = .UsedRange.Value     'convert formulas to values
            .Name = .Range("B1").Value              'name copied sheet
        End With
    Next
    Application.ScreenUpdating = True
End Sub

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.

Sub Olivia()
Dim ws As Worksheet:    Set ws = Sheets("Main Sheet") 'you are going to need to change this so it matches your sheet name
Dim newsht As Worksheet
Dim icell As Range

Application.ScreenUpdating = False
For Each icell In Range("IStateMarket")
    ws.Range("A4").Value = icell.Value
    ws.Calculate
    Set newsht = Worksheets.Add(After:=Sheets(6))
    If Not ws.Range("B1").Value = "" Then
        newsht.Name = ws.Range("B1").Value
    Else
        newsht.Name = "Error " & icell.Value
    End If
    ws.UsedRange.Copy
    newsht.Range("A1").PasteSpecial Paste:=xlPasteValues
Next icell
Application.ScreenUpdating = True

End Sub

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

To Alphafrog,

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

Olivia