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?
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.