How to print multiple copies with sequence page numbers?
Have you ever encountered a situation where you need to print several copies of the same worksheet, but want each copy to be labeled with a unique, sequential page number? For example, you might require10 copies of a form, with cell A1 showing001 on the first printout,002 on the second, and so on up to010 on the last copy. This approach is helpful in scenarios such as producing numbered invoices, tickets, order forms, or any batch where tracking individual copies is necessary for organization or auditing purposes.
Unfortunately, Excel does not provide a built-in setting that allows users to automatically print multiple copies with incremented numbers in a specified cell. Manually editing the sheet for each printout is not practical, especially when dealing with a large number of copies. However, by applying a simple VBA macro, you can automate this process to save time, reduce manual errors, and ensure every copy is uniquely numbered as required.
Print copies with different numbers by VBA code
Use Excel Formulas and Manual Printing
Print copies with different numbers by VBA code
Print copies with different numbers by VBA codeThis method utilizes a VBA macro to print multiple copies of a worksheet, automatically inserting sequential numbers into a designated cell for each printout.
Follow these steps to quickly create and execute the macro:
1. First, activate the worksheet that you want to print. Then, press Alt + F11 to open the Microsoft Visual Basic for Applications (VBA) editor window.
2. In the VBA editor, click Insert > Module to add a new module. Copy and paste the following code into the module window. (Refer to the screenshot for visual guidance.)
VBA: Print copies with sequence page numbers.
Sub PrintCopiesWithNumbers()
'UpdatebyEntendoffice20161031
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 10
Range("A1").Value = "'00" & i
ActiveSheet.PrintOut
Next i
Application.ScreenUpdating = True
MsgBox "Total printed copies: " & (i - 1), vbInformation, "Kutools for Excel"
End Sub
Parameter notes and tips:
- In the code, the reference A1 indicates the cell where the sequence number will appear. If you prefer another cell, such as B2, update the reference accordingly.
- 1 To10 specifies the number of copies to print (in this example,10). You can change10 to any other positive integer as required.
- "'00" & I formats the sequence number to display as three digits with leading zeroes (e.g.,001,002,003). If you want a different format (such as starting from100, or without leading zeroes), adjust the string accordingly.
- Before running the macro, ensure your worksheet is ready and contains no data that could be overwritten in the target cell. It’s recommended to save your workbook before proceeding, as macro actions cannot be undone.
3. With your worksheet still active, press F5 or the Run button within the VBA editor to execute the code. A dialog box will appear, notifying you that, for example, "10 copies will be printed with sequence numbers." Click OK to proceed; the macro will automatically cycle through each number, populate the sequence in your designated cell, and print the sheet. Each printout will be uniquely numbered per your settings.
Use Excel Formulas and Manual Printing
In scenarios where you only need a limited number of copies or prefer to avoid macros, you can use Excel’s fill handle to generate sequential numbers and then manually print each row. This method is particularly applicable when unique numbering is required for labels, badges, or forms across a manageable batch size.
1. In a new column (for example, column A), enter the starting sequence number in the first cell, such as001. You can do this by entering the following formula in cell A1:
=TEXT(ROW(A1),"000")2. Drag the fill handle down to replicate the formula for as many copies as you wish to print (for example, down to row10 for10 copies). Each cell will display the next sequential number with leading zeros.
3. Copy the entire row or data set to a new sheet for each required copy, and use Excel’s Print function to print the sheets individually. This manual method works best for smaller batches and provides greater visual confirmation before printing.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in