Snasui Excel Forum

Code: Select all

Sub Print_List()
  Dim strValidationRange As String
    Dim rngValidation As Range
    Dim rngDepartment As Range
     
     ' Turn off screen updating
    Application.ScreenUpdating = False
     
     ' Identify the source list of the data validation
    strValidationRange = Range("i4").Validation.Formula1
    Set rngValidation = Range(strValidationRange)
     
     ' Set the value in the selection cell to each selection in turn
     ' and print the results.
    For Each rngDepartment In rngValidation.Cells
        Range("i4").Value = rngDepartment.Value
        ActiveSheet.Printout
    Next
     
    Application.ScreenUpdating = True
End Sub