How to auto increment cell value after each printing?
In many business and administrative workflows, there may be a need to print out multiple copies of a document, invoice, or check, each labeled with a unique serial number or identifier for easy record-keeping and tracking. For example, imagine you have an Excel worksheet formatted as a check or voucher template, and cell A1 contains a check number such as "Company-001". When you print 100 copies of this page, it would be tedious and error-prone to manually update the number for each individual printout. Ideally, you would want Excel to automatically increment the value in cell A1 for each print action, so the first print shows "Company-001", the second "Company-002", and so on, up to "Company-100".
By default, Excel does not provide a built-in feature to automatically increase a cell value after each print job. However, you can achieve this automation with the help of a VBA macro. Using VBA is a practical solution if you need to efficiently generate consecutively numbered hard copies, especially when working with serial forms, tickets, checks, or any scenario requiring unique identifiers for printed pages. This approach helps avoid manual errors, saves time, and guarantees each copy receives a unique number as needed.
Below, we introduce a VBA method to auto-increment cell values for each printed copy. Additionally, this article offers guidance on effective usage, potential caveats, and practical tips for optimizing batch printing tasks in your Excel workflow.
Auto increment cell value after each printing with VBA code
Auto increment cell value after each printing with VBA code
There is no direct, native Excel option for auto-incrementing a cell value with every printout. To automate this process, you can use a VBA macro to efficiently print out a specified number of copies, each with an incremented identifier in cell A1. This method is especially suited for generating personalized hard copies (such as checks, labels, forms, etc.) with sequential numbering. Please note that using macros may require enabling macros within your Excel environment, and running VBA code can affect file security settings. Always make sure to save your work before running any code.
1. Hold down the ALT + F11 keys together to open the Microsoft Visual Basic for Applications window. If this is your first time using VBA, you might need to enable the Developer tab in your Excel ribbon.
2. In the VBA editor, click Insert > Module to open a new module window. In the new module, copy and paste the VBA code provided below. This script will help you increment the printout number for every copy you print from the active worksheet.
VBA code: Auto increment cell value after each printing:
Sub IncrementPrint()
'updateby Extendoffice
Dim xCount As Variant
Dim xScreen As Boolean
Dim I As Long
On Error Resume Next
LInput:
xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
If TypeName(xCount) = "Boolean" Then Exit Sub
If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
GoTo LInput
Else
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = 1 To xCount
ActiveSheet.Range("A1").Value = " Company-00" & I
ActiveSheet.PrintOut
Next
ActiveSheet.Range("A1").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub
This code prompts you for the total number of copies you wish to print, handles the updating of cell A1 for each print cycle, and prints each version automatically.
3. After pasting the code, make sure your worksheet is ready and the active cell (typically A1) contains the correct base text (like "Company-001"). Press the F5 key or click Run in the VBA editor to start the macro. A prompt box will appear asking how many copies you wish to print; enter the number desired (for example, 100) and confirm the input. Please see the interface screenshot below for reference:
If you input a non-numeric value or click Cancel, the process will stop with no action. Ensure you only enter numbers and double-check the starting value in cell A1 before proceeding.
4. Click the OK button. The macro will now automatically print the worksheet the specified number of times, adjusting the sequence in cell A1 each time so that each printed page is numbered in order (e.g., Company-001, Company-002, Company-003, ... up to Company-100, depending on your input and the formatting in the macro).
Additional notes and tips: In the code, cell A1 is used for the serial number. Any existing content in A1 will be overwritten by the first new sequence number, so if you have information you wish to keep, make sure to back up this cell before running the code. The default numeric prefix ("Company-00") can be adjusted in the VBA code to match your preferred serial format; change both the text and the numeric padding as desired. Be cautious when using the macro in shared or protected workbooks, as macro execution requires the proper permissions. If your printer is slow, avoid running the script with very large numbers of copies to prevent overwhelming the print queue.
If you encounter issues such as the print job not starting, numbers not incrementing correctly, or errors regarding cell references, double-check that macros are enabled and your worksheet is not protected. Make sure you have set the reference cell (A1 in this example) correctly before running, and consider saving your workbook before batch operations to avoid unintentional data loss. If further customization is required (such as using a different cell, number format, or worksheet), adjust the cell references and formatting lines in the VBA code as appropriate.
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