Forum Discussion

UKHG-AJ's avatar
UKHG-AJ
Copper Contributor
May 31, 2023

Trying to print all options from a VLOOKUP

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

  • 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
    

     

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    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
    

     

    • UKHG-AJ's avatar
      UKHG-AJ
      Copper Contributor
      Thank you so much! That worked perfectly!

Resources