How to auto increment cell value after each printing?
Supposing, I have a worksheet page needed to be printed 100 copies, the cell A1 is the check number Company-001, now, I would like the number will increase by 1 after every printout. That means when I print the second copy, the number will be increased to Company-002 automatically, the third copy, the number will be Company-003…one hundred copy, the number will be Company-100. Is there any trick to solve this problem in Excel quickly and possibly?
Auto increment cell value after each printing with VBA code
Auto increment cell value after each printing with VBA code
Normally, there is no direct way for you to solve this task in Excel, but, here, I will create a VBA code to deal with it.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
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
3. Then press F5 key to run this code, and a prompt box is popped out to remind you enter the number of copies that you want to print the current worksheet, see screenshot:
4. Click OK button, and your current worksheet is printing now, and at the same time, the printed worksheets are numbered Company-001, Company-002, Company-003…in cell A1 as you need.
Note: In the above code, the cell A1 will be inserted the sequence numbers that you ordered, and the original cell value in A1 will be cleared. And “Company-00” is the sequence number, you can change them to your need.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
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!
