Macro Loop for printing off drop down menu

Question

I have a sheet that has a drop down list with athletes names in it. Once selecting a name, the field changes to meet the individual capabilities of the athlete. I need to print off a various number of these sheets on a weekly basis (its dependent on how many people are in the original list as we have different teams using the sheet). It would be quite helpful to have a macro that would read all the names and print the individual sheets for each athlete. The list of athlete names is listed in sheet 1RM cell A3-A152 (not all cells are filled and I don't want to print 150 blank sheets every time). The drop down menu is located on another sheet in cell B3 (there are multiple copies of this sheet, all with different names). Essentially, I need the macro to change the name in cell B3, print, them move onto the next, print and repeat until it reads a blank (signifying the end of the roster). Thank you for any and all help.

Answer 1

Use a FOR EACH loop and get all values from your list, store each in the cell with the validation list and printout the sheet.

Andreas.

Sub Test()

  Dim R As Range

  For Each R In Sheets("Whatever").Range("A3:A152")

    If Not IsEmpty(R) Then

      Range("B3") = R

      ActiveSheet.PrintOut

    End If

  Next

End Sub