How to Print Every Data Validation Drop-Down Option in WPS Spreadsheets with VBA
Question details
The user needs a VBA macro that cycles through unique nonblank contract numbers from a data validation source, places each value into a template cell, and prints one completed contract for each option.
- Product
- WPS Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- A contract template contains a data validation drop-down list, and the user wants to generate a printed copy for every unique nonblank contract number automatically.
- Observed behavior
- The desired behavior is an automated print loop that updates the validation cell and calls PrintOut once per value, without manually selecting each drop-down option.
Identify the source sheet name, source range, template sheet name, and validation cell address in your workbook. Enable the Developer tab in WPS Spreadsheets so the Visual Basic editor is available.
Use a VBA Collection to Print Each Unique Drop-Down Option
Recommended when you want one macro to collect unique, nonblank values and print the template once per value without manual copying or repeated print setup.
This method stores drop-down source values in a VBA Collection, which ignores duplicate keys, and then loops through the Collection to update the template cell and call PrintOut. It works best when the source values are on one sheet and the template print area is already configured.
In WPS Spreadsheets, click the Developer tab and select Visual Basic. If the Developer tab is not visible, enable it from File > Options > Customize Ribbon.
In the VBA editor Project pane, right-click VBAProject, choose Insert > Module, and open the blank code module.
Paste the following macro into the module: Sub PrintAllContracts() Dim values As New Collection, c As Range, item As Variant On Error Resume Next For Each c In Worksheets("Sheet1").Range("A2:A10") If Len(Trim(c.Value2)) > 0 Then values.Add Trim(c.Value2), Trim(c.Value2) Next c On Error GoTo 0 For Each item In values Worksheets("Sheet2").Range("A15").Value = item Worksheets("Sheet2").PrintOut Next item End Sub Replace Sheet1, Sheet2, A2:A10, and A15 with your workbook's actual names and addresses.
Set Worksheets("Sheet1").Range("A2:A10") to the sheet and range that contains the drop-down source values, and set Worksheets("Sheet2").Range("A15") to the data validation cell on your contract template sheet.
Click inside the PrintAllContracts procedure and press F5, or go to Run > Run Sub/UserForm. Each unique value is written to the template cell and the sheet prints through the configured printer.
Print Every Drop-Down Option Automatically in WPS Spreadsheets
WPS Spreadsheets includes a VBA editor that can run this contract-printing macro directly. You can loop through each unique drop-down value, update the template cell, and print one copy per option without additional Office automation tools.
- 1. Enable the Developer tab: Open WPS Spreadsheets, click File > Options > Customize Ribbon, and enable Developer.
- 2. Open the VBA editor: Click Developer > Visual Basic to open the VBA coding window.
- 3. Insert a module and paste the macro: Right-click the workbook project, select Insert > Module, and paste the PrintAllContracts code.
- 4. Adjust source range and validation cell: Edit the worksheet names, source range, and template cell in the code to match your workbook.
- 5. Run the macro and print: Click inside the macro and press F5 to update the validation cell and print one sheet for each unique value.
Frequently Asked Questions
Does this macro skip blank cells in the data validation source?
Yes. The condition If Len(Trim(c.Value2)) > 0 ignores blank cells, so only nonblank values are added to the Collection.
Can I use a named range instead of A2:A10 for the source list?
Yes. After creating a named range such as ContractNumbers, replace Worksheets("Sheet1").Range("A2:A10") with Range("ContractNumbers") in the macro.
Will the macro print one page per drop-down option?
It calls PrintOut for each value. The printed output depends on the template sheet's print area and page setup, so set the print area to the contract area before running the macro.
Where do I run the VBA code in WPS Spreadsheets?
Click Developer > Visual Basic, insert a module, paste the code, then click inside the Sub and press F5 or use Run > Run Sub/UserForm.
