Run macro using drop down list

[tezza]

Hi All

I'm looking to run and repeat a macro the uses data from a drop down list until the list is completed, here's the details.

On Sheet 2 B1 a drop down list grabs data from AS1 down, here's a breakdown of what I'm trying to achieve:

Step 1: select the first item on the drop down list
Step 2: run a macro call 'grab'
Step 3: run a macro called 'send'
Step 4: move onto the next item in the list, rinse and repeat until the full drop down list has been done.

All this is actually doing is selecting a site, filtering the data from sheet 1 and putting it into sheet 2 then sending that data to a new workbook ready to be emailed off.

Hope you can help
Regards
Tezza

 

[footoo]

Dim rng As Range, cel As Range
Set rng = Evaluate([B1].Validation.Formula1)
For Each cel In rng
    [B1]=cel.Value
    Call grab
    Call send
Next

 

[tezza]

Hi

Thank you for this. What I'm now finding is that after sending the data to a new workbook the code tries to then run from the new workbook, any idea how you get around that.

When I run my send macro it automatically puts me into the new workbook also.

Regards

 

[footoo]

Unable to comment without knowing what your macros do.

Try :

Dim rng As Range, cel As Range
Set rng = Evaluate(Sheets("Sheet1").[B1].Validation.Formula1)
For Each cel In rng
    Sheets("Sheet1").[B1]=cel.Value
    Call grab
    Call send
Next