WPS Office VBA Code to Search a Combo Box Value and Copy a Matching Row
Question details
The user wants a VBA macro that selects a customer code from a combo box, searches for that code in another workbook, copies the matching row, and pastes it transposed into a destination sheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating a customer code lookup between two workbooks.
- Observed behavior
- The intended goal is for the macro to open the master workbook, locate the matching row, and paste the result transposed with headings into the destination sheet.
Before running the macro, confirm the master workbook path, sheet name, and the column that contains the customer codes. Test with sample files or copies before using production data.
Create a combo box and button that run a VBA lookup macro
Recommended when you need a reusable worksheet control to select a customer code and copy the matching row transposed.
This approach uses an ActiveX combo box and a command button on the worksheet. The macro opens the master workbook, searches the customer-code column, and writes the matching row plus headings to the destination sheet.
Click the Menu button, choose Options, open Customize Ribbon, enable Developer, and then click OK.
On the Developer tab, click Insert, choose Combo Box from the ActiveX controls, and draw it on the worksheet. Repeat the same process to add a Command Button and set its caption to Search.
On the Developer tab, click Visual Basic, right-click the project in the Project Explorer, choose Insert, and then choose Module. Paste the lookup code into the module.
Set the master workbook path, master sheet name, search column, and destination sheet name to match your files. Right-click the command button, choose Assign Macro, and select the lookup macro.
Select a customer code from the combo box, click Search, and confirm that the matching row is pasted transposed with headings into the destination sheet.
Use a data-validation dropdown and button with the same lookup macro
Use this option if you prefer a simpler cell-based dropdown instead of an ActiveX combo box.
Automate the combo box lookup with WPS Office Spreadsheets
You can build the same VBA workflow in WPS Office Spreadsheets: add a combo box and button, assign a lookup macro, and copy a matching row transposed into your destination sheet.
- 1. Download and open WPS Office: Download WPS Office from the official site, open WPS Spreadsheets, and load the workbook that will contain the combo box and button.
- 2. Enable the Developer tab: Click Menu, choose Options, open Customize Ribbon, enable Developer, and click OK.
- 3. Insert controls and paste the VBA code: Insert a combo box and command button, open the Visual Basic Editor, add a module, and adapt the lookup macro to your workbook paths and sheet names.
- 4. Run the macro and verify the transposed row: Select a customer code, click the button, and check that the matching row appears transposed with headings in the destination sheet.
Frequently Asked Questions
Does WPS Office support VBA macros?
WPS Spreadsheets supports VBA and macros in Windows versions. Open the Developer tab and use the Visual Basic Editor to add or edit macro code.
Why does my macro not find the customer code?
Check the master workbook path, master sheet name, search column, and destination sheet name in the code. Also check for leading or trailing spaces, case differences, or numbers stored as text.
Can I use a data-validation list instead of an ActiveX combo box?
Yes. Create a list through Data > Validation, then assign the same lookup macro to a command button or worksheet event.
How do I paste the matching row transposed?
In the VBA code, use PasteSpecial with Transpose set to True, or write the matching cell values directly to the destination range in transposed order.
How do I assign the macro to a button or combo box?
Right-click the button or combo box, choose Assign Macro, select the macro name, and click OK.
