Forum Discussion
UKHG-AJ
May 31, 2023Copper Contributor
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
- JKPieterseSilver 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-AJCopper ContributorThank you so much! That worked perfectly!