Macro to update from a list and print each time

[David Sutcliffe]

Hi everyone, hopefully you can help me as I'm a bit of a macro newbie.

I need what I imagine is a fairly simple macro - updating a cell from a list and automatically printing out the tab each time, so pressing one button will update a sheet with each entry in the list and print them all off.

How it's set up:

Cell E5 is the definining cell that my sheet looks up all data from. Cell E5 is a validation list from a range AH6:AH30 (AH6:AH30 can have different number of actual names to put into E5, returning 0 for all the extra spaces down to AH30 if there aren't enough names)

I want to be able to add a big button that I can press that will automatically update E5 to the top name from the list, print the tab, then replace E5 with the next name in the list, print it, replace E5, print it... all the way down until the next name in the list is "0" when it knows to stop.

The possible complication is that I already have a macro in the sheet that looks to see when E5 is changed, and then runs around triggering other smaller macros to update the various part of the sheet. I still need those macros to run each time I update E5 BEFORE I print the sheet.

If Target.Address = "$E$5" Then
   Range("C26").Select
    ActiveCell.FormulaR1C1 = "=R[-16]C"
    Range("C27").Select
    Range("W11:W21").Select
    Selection.ClearContents
    Range("E6").Select
End If

Can anybody help me? Could you write me the macro I need (I don't ask for much, do I?)... and do I need to somehow embed this in the existing macros or will it happily run and allow all the other macros to complete before it hits Print?

Thanks in advance,
Dave