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...
- May 31, 2023
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
May 31, 2023Silver 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-AJMay 31, 2023Copper ContributorThank you so much! That worked perfectly!