VBA Code to select each item in turn from a validation list and then run some other code

Hi There,

I have worksheet that uses a validation list as the source for various lookup functions, the end result is a dynamic report, I now intended to email this out automatically, I already have the code to create an email and attach the data in the body. However as the data is sensitive I need the data to be emailed to one person, per entry in the validation list - again all this has been coded correctly, in that when I manually select an entry the appropriate data is refreshed and an email generated with the correct recipient.

Unfortunately i'm having a little mental block on how to go each item within the validation list, as there are currently 400 entries (set to grow), its quite a laborious process (albeit even now still faster than the previous method used).

For reference purposes the validation list exists in a sheet named "STATEMENT" and CELL D2

any help would be greatly received, thank you.

Make sure you are putting the code in the right spot:

Please Login or Register  to view this content.

Hi Bernie,

I'm still getting the erroneous entries i'm afraid, even after implementing these changes, I should point out that the agent names on my sheet are generated by looking at another sheet via

Please Login or Register  to view this content.

Hi Bernie,

Thanks for your perseverance, I Have a header in cells A1:A2 (merged) first cell that has data is A3, and no skipped cells, I have therefore changed the definition to =OFFSET(Overview!$A$3,0,0,COUNTA(Overview!$A:$A)-1,1), bit get a run time error 1004 when stepping through at the point: Worksheets("Statement").Range("D2").Value = r.Value

You sir are an official Legend!

Thank you so much for your time and for helping me with this.