May 31 2023 03:17 AM
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
May 31 2023 05:11 AM - edited May 31 2023 05:11 AM
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
May 31 2023 05:48 AM
May 31 2023 05:11 AM - edited May 31 2023 05:11 AM
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