How to Create an Excel VBA Macro to Find a UPC and Copy Product Details
Question details
The user wants to use an Excel VBA macro triggered by a button to search for a UPC code in one worksheet and copy the matching item name and price to a second worksheet formatted for printing labels.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific product's details using its UPC and extracting that data to automatically generate a printed product label.
- Observed behavior
- The user needs a script and setup instructions to automate finding and copying UPC details, and formatting them for a 2.25-inch by 1.25-inch label layout.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon to access VBA features.
Create and Assign a VBA Macro to Search for UPCs
Use a custom VBA script leveraging Application.InputBox and Application.Match to find the requested UPC and transfer the data to the label printing sheet.
This solution uses a built-in prompt to ask the user for a UPC code. It then searches for this code in Column A of the source sheet. If a match is found, it copies the corresponding product details to specific cells on the destination sheet, which is pre-formatted for your label printer.
Press Alt + F11 on your keyboard to launch the VBA Editor. Click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.
Paste the following logic into the module: Declare your variables, use 'Application.InputBox' to prompt the user for the UPC, and 'Application.Match' to search Worksheets("Sheet1").Columns("A"). Finally, write the found values to Worksheets("Sheet2").Range("A1") and Range("A3").
Return to your Excel worksheet. Navigate to the Developer tab, click 'Insert', and choose 'Button (Form Control)'. Draw the button on Sheet1 and assign your newly created UPC macro to it.
Go to Sheet2 and adjust the row heights and column widths to match your 2.25-inch by 1.25-inch label dimensions. Increase the font size for cells A1 and A3 so the product name and price are highly visible when printed.
Use WPS Spreadsheet to Automate Data Extraction with Macros
WPS Office fully supports VBA macros, allowing you to easily run scripts like the UPC search tool. You can automate repetitive data extraction and label printing tasks efficiently using its built-in developer tools.
- 1. Open your Workbook in WPS Office: Launch WPS Spreadsheet and open your product database workbook. Navigate to the 'Developer' tab on the ribbon.
- 2. Launch the VBA Editor: Click 'Macros' or press Alt + F11 to open the built-in VBA Editor.
- 3. Add the UPC Macro: Insert a new module, paste your UPC search macro code, and save the document as a macro-enabled workbook.
- 4. Assign the Macro to a Button: Use the 'Insert' option under the Developer tab to place a form button on your sheet, linking it to your macro for single-click execution.
Frequently Asked Questions
Why am I getting a 'UPC not found' error even when the number exists?
This typically occurs due to a data type mismatch. Ensure that the UPC column is formatted consistently (either entirely as text or entirely as numbers) and that there are no hidden leading or trailing spaces in your cells.
How can I adjust the VBA code if my UPCs are in a different column?
In your VBA code, modify the search range reference. For example, change Worksheets("Sheet1").Columns("A") to the appropriate column letter where your UPCs are stored, such as Columns("D").
Can I print the label automatically immediately after the macro runs?
Yes, you can automate printing by adding the command Worksheets("Sheet2").PrintOut just before the End Sub statement in your VBA code.
