VBA & Macro Problems

How to Create Parts Labels Automatically in Excel with VBA

Maira Mehtab Sep 30, 2026 868 views

Question details

The user needs a method to generate multiple packing labels automatically using Excel VBA, specifically by dividing a total order quantity into specified pack sizes and outputting the results to a separate sheet.

How to Create Parts Labels Automatically in Excel with VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating printable packing labels for parts based on total order quantities and pack sizes from an input worksheet.
Observed behavior: The user requires an automated macro to divide total quantities by pack quantities and output the exact number of labels required (including full packs and remainders) onto a separate Labels worksheet.

Before you start

Ensure you have enabled the Developer tab in Excel and adjusted your Macro Security settings to allow VBA scripts to run.

Solution 1Recommended

Write a VBA Macro to Split Quantities and Generate Labels

Create a custom macro that loops through the input rows, calculates the number of full packs, and writes individual label rows to a separate sheet.

By utilizing a For loop and mathematical operators like division and Mod (modulo), VBA can determine exactly how many full packs and remainder labels are needed for each order row.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

In the top menu, click Insert and select Module to create a blank script window for your code.

Write the Macro Logic

Write a script that reads data from your 'INPUT' sheet. Loop through the rows, dividing the total quantity by the pack quantity to get the number of full labels. Use the 'Mod' operator to check if a remainder label is needed.

Output to the Labels Sheet

Instruct the macro to write the part number, description, and pack quantity to consecutive rows on the 'Labels' worksheet for each full pack, followed by a final row for any remainder quantity.

Run the Macro

Press F5 or click the Run button in the toolbar to execute the macro and generate your parts labels.

Write a VBA Macro to Split Quantities and Generate Labels

Saving your File: Remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) so your VBA code is not lost.

WPS Spreadsheet Automation

Generate Packing Labels Seamlessly Using WPS Spreadsheet VBA

WPS Office provides robust and comprehensive VBA support in its Spreadsheet application, allowing you to run your label-generating macros exactly as you would in Microsoft Excel.

1. Open the Developer Tools: Launch WPS Spreadsheet, go to the Developer tab, and ensure macros are enabled.
2. Access the VBA Editor: Click the VBA Editor icon to open the scripting environment and paste your label generation code.
3. Run Your Script: Execute the macro to automatically read your input sheet and generate the packing labels on your designated output sheet.

Fully compatible with Microsoft Excel VBA syntax and macrosEasily save and open standard .xlsm and .xlsb formats without formatting lossLightweight installation with fast script executionFree built-in developer tools for everyday automation tasks

Frequently Asked Questions

How do I handle remainders if the total quantity isn't perfectly divisible by the pack size?

You can use the Mod operator in VBA (e.g., TotalQty Mod PackQty) to calculate the remainder. Add an If statement in your code to print one final label with this remaining quantity if the Mod result is greater than zero.

Why is my VBA macro not running in Excel?

Ensure that macros are enabled in your Trust Center settings (File > Options > Trust Center > Trust Center Settings > Macro Settings) and that you are working in a Macro-Enabled Workbook (.xlsm).

Can I format the output labels for printing directly from VBA?

Yes, you can add VBA commands to adjust row heights, column widths, fonts, and page setup settings on the Labels worksheet so they align perfectly with your physical label sheets.

Excel & Data Problems