Help With Dropdown List Macro!

[eririt]

I have an excel file used for pulling store sales data.

User selects 1 store from a list of 3,000 stores via a dropdown list. The workbook then displays that stores 1-page summary of sales information.

I have to pull information from 120 of the same stores each week.

Searching through a list of 3,000 stores to pick the one I want takes hours.

I would like to use a macro that would select each store from my list & print each page, one by one.

Any guidance would be greatly appreciated!

Thanks

 

[jbeaucaire]

Assuming things like:

1. The sheet with drop down in it is Sheet1, cell A1.
2. The sheet with the list of stores you want to use is Sheet2, column A starting at A1 and going down

...Then this basic macro will do that, edit to your exact needs.

Sub PrintManyForms()
Dim MyStores As Range
Dim Store    As Range

Set MyStores = Sheets("Sheet2").Range("A:A").SpecialCells(xlConstants)

With Sheets("Sheet1")
    For Each Store In MyStores
        .Range("A1").Value = Store.Value
        .PrintOut Copies:=1
    Next Store
End With
        
End Sub

 

[eririt]

Thanks for the help. While adding the macro, I found that the dropdown is actually an object named "Drop Down 11"

Is it possible to edit the macro to select the store # from an object?

Thanks

 

[jbeaucaire]

Total guess here... something like:

.Shapes("Drop Box 11").Value = Store.Value