Macro to email all drop list selections

[Mara]

Hi,
I have spreadsheet comprising of 20 pages worth of data for over a hundred
stores. I've created a summary page with a drop box enabling each store to
select their store name and then the page will auto populate their figures
for the 20 reports (used vlookup formulas against the 20 pages to bring back
the stores data). The page also includes in a graph detailing the stores
previous 8 weeks results.
Also included on this page is an email option.

What I would like is to have a macro that will select each store from the
drop list and auto email this page opposed to manually selecting over a
hundred stores and emailing individually. I've tried recording, however it
doesn't pick up the function when I'm selecting a new store.

Any help would be much appreciated !!

 

[Sheeloo]

Do something like assuming your dropdown is in A1 of Sheet1 and you have a
list of stores on the activesheet in A1:A100

Sub t()
For i = 1 To 100
'Populate the drop-down cell with each Store name
Worksheets("Sheet1").Cells(1, 1) = ActiveSheet.Cells(i, 1)
'Force recalculation
Application.Calculate
'Call you email macro to send the current store
Call emailmacro
End Sub