Print All Options in drop-down List

This post will guide you how to print all options in a drop down menu list in a worksheet. How do I print all options in a drop down menu into separate printout in excel. How to print all options in a drop down menu with VBA code in excel.

For example, there is a spreadsheet that give you profit for each of 20 branch companies depending which one you pick from a drop down menu. And what you want to do is that how to print all these 20 branches from the drop down menu list without manually picking each item and printing.

Print All Options in drop-down List

To print all options in a drop down menu list in a worksheet into separate printout, just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub printAllOptions()
    Dim dvCell As Range
    Dim inputRange As Range
    Dim c As Range
    'Which cell has data validation
    Set dvCell = Worksheets("Sheet1").Range("B1")

    'Determine where validation comes from
    Set inputRange = Evaluate(dvCell.Validation.Formula1)

    For Each c In inputRange
        dvCell = c.Value
        'add some print page code here
    Next c
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.