How to Create Sequential Numbered Labels in Excel using VBA
Question details
The user needs a method or macro to generate and print a specific quantity of sequential numbered labels (e.g., 3001 to 4050) for containers without wasting sticker sheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Printing large batches of sequential numbered labels for physical containers.
- Observed behavior
- The user requires an automated process to generate increasing numbers on a specific label layout for printing.
Before writing your VBA macro or setting up Mail Merge, ensure you know your exact label sheet layout (number of rows and columns per page) and the physical dimensions of your stickers.
Generate Sequential Labels using Excel and Word Mail Merge
The easiest way to create sequential labels without writing VBA code is to generate the numbers in Excel and use Word's Mail Merge feature to format the label layout.
This method requires no programming knowledge and allows you to use pre-defined templates for almost any commercial label brand.
Open Excel, type your starting number (e.g., '3001') in cell A1. Use the Fill handle to drag down to row 1050 (or your desired end number), and save the workbook.
Open Word, go to the Mailings tab, click 'Start Mail Merge', and select 'Labels'. Choose your specific sticker layout brand and product number.
Click 'Select Recipients', choose 'Use an Existing List', and select the Excel file you just saved.
Click 'Insert Merge Field' into the first label. Then, click 'Update Labels' to populate the entire sheet with the sequential placeholders.
Click 'Finish & Merge' and select 'Print Documents' to send your generated sequential labels directly to your printer.
Create a Custom VBA Macro for Sequential Labels
For advanced automation directly in Excel without using Word, you can use a VBA script to loop through numbers and format them into your specific label grid.
Create and Print Sequential Labels Easily with WPS Office
WPS Spreadsheet and WPS Writer offer powerful, built-in tools like AutoFill and Mail Merge that make creating sequential numbered labels effortless. You can easily set up your data and print perfectly aligned stickers without needing complex VBA code.
- 1. Create numbers in WPS Spreadsheet: Open WPS Spreadsheet, enter your starting number, and use the Fill tool to generate your complete number sequence. Save the file.
- 2. Start Mail Merge in WPS Writer: Open a new blank document in WPS Writer, navigate to the Mailings tab, and click on Mail Merge.
- 3. Import and Print: Select your label template, import your saved Spreadsheet file, insert the merge field, and click Print.
Frequently Asked Questions
Can I add prefixes or suffixes to my sequential label numbers?
Yes. In Excel, you can use a custom number format (e.g., 'BOX-'0000) or use a formula like `="BOX-" & A1` to add text before or after your sequential numbers.
How do I prevent wasting labels on a partially used sticker sheet?
When using Mail Merge, you can leave blank cells at the top of your Excel data list. If you are missing 3 stickers on your physical sheet, leave the first 3 rows blank in Excel so printing starts at the 4th position.
Why is my VBA macro printing blank pages between labels?
This usually happens if the macro sets the print area larger than the populated cells, or if the row height and column width exceed the physical paper size. Double-check your Page Setup, margins, and page break settings.
Can I print barcodes instead of just text numbers?
Yes. You can install a barcode font (such as Code 39) on your computer. Format your sequential numbers to use that specific font in Excel or Word, and the numbers will print as scannable barcodes.
