Trying to print all options from a VLOOKUP

[UKHG-AJ]

I have created a new spreadsheet that contains all the details for our supplier contracts.

All the Data is on Tab 1, i have then created a contract on Tab 2 where you type select the contract number from Tab 1 via a dropdown menu (Data Validation list in cell A15 of Tab 2) and the rest of the contract is populated by lookup formulas. 

However some days we have so many suppliers we need to print every variable from the dropdown.

Is there a way to print every option from the dropdown in one go (as opposed to manually changing it each time?)

There are also a lot of blanks and would like to exclude those.

Thanks

[#2 · Accepted solution]

@UKHG-AJ This code should do the trick. Adjust cell addresses and worksheet names in the code before running this on a copy of your file.

Sub PrintAllContracts()
    Dim contractNmbr As String
    Dim contracts As Collection
    Dim contractCell As Range
    Dim contract As Variant
    Set contracts = New Collection
    For Each contractCell In Worksheets("Sheet1").Range("A2:A10")
        contractNmbr = Trim(contractCell.Value2)
        If Len(contractNmbr) > 0 Then
            On Error Resume Next
            'Add only unique contract numbers
            contracts.Add contractNmbr, contractNmbr
        End If
    Next

    If contracts.Count > 0 Then
        For Each contract In contracts
            With Worksheets("Sheet2")
                .Range("A15").Value = contract
                .PrintOut
            End With
        Next
    End If
End Sub

[#3]

Thank you so much! That worked perfectly!