Note: The other languages of the website are Google-translated. Back to English
English English

 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


arrow blue right bubble 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:

doc increment when printting 1

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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (51)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This code is amazing, it is exactly what I need, however, I was wondering if there is a way to start printing from the number that is entered in cell "A1"? For example, if I have printed 100 copies, on the next print run I will need to print from number 101 and count up from there. I have tried a few code adjustments but it only seems to take the number entered in the cell i.e. 101, add 1 and then the rest of the prints are stuck with that one number, i.e. 102... Your assistance would be greatly appreciated :-)
This comment was minimized by the moderator on the site
If you did't find a solution already you can edit line 17 of the code to this: ActiveSheet.Range("A1").Value = Range("A1").Value + 1
This will ad +1 to the number you have in A1 cell.
This comment was minimized by the moderator on the site
It does not send to my printer
This comment was minimized by the moderator on the site
Bonjour,

en exécutant la macro ça efface le nombre de ma cellule.
Je voudrais par exemple avoir A1= 153, je lance une impression de 10 copies. J'ai dis feuilles imprimée de 154 à 164 ET je voudrais que le nombre de la cellule soit aussi 164.
Comme ça quand je relance une impression ça prend le chiffre dans A1.
J'aimerais aussi si possible na pas à avoir aller dans basic. je voudrais que la macro s'active directement via l'option impression. C'est possible ?
This comment was minimized by the moderator on the site
Bonjour,

en exécutant la macro ça efface le nombre en A1.

je voudrais si c'est possible par exemple A1=153 et faire une impression de 10 copies. donc je récupéré 10 impressions numérotées de 154 à 164 ET je voudrais aussi que le 153 en A1 s’incrémente jusqu’à 164.

Je voudrais aussi si possible ne pas à avoir utiliser basic pour l'impression. je voudrais pouvoir declancher directement la macro en utilisant l'option impression tout simplement.

Merci pour votre aide
This comment was minimized by the moderator on the site
Hello, kaji,
To solve your proble, please apply the below code:
Sub IncrementPrint_Num()
Dim xCount As Variant
Dim xScreen As Boolean
Dim I As Long
Dim xInt As Integer
On Error Resume Next
xInt = 153 'number
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
xInt = xInt + 1
ActiveSheet.Range("A1").Value = xInt
ActiveSheet.PrintOut
Next
Application.ScreenUpdating = xScreen
End If
End Sub

Please have a try, hope it can help you, if you have any other problem, please comment here.
This comment was minimized by the moderator on the site
Find attached the modified codes.

And here it is in text:
Sub IncrementPrint()
'updateby Extendoffice
Dim xEnd As Variant
Dim xStart As Variant
Dim xScreen As Boolean
Dim I As Long
On Error Resume Next
LInput:
xStart = Application.InputBox("Please enter the first number:", "Kutools for Excel")
xEnd = Application.InputBox("Please enter the last number:", "Kutools for Excel")
If TypeName(xCount) = "Boolean" Then Exit Sub
If (xStart = "") Or (Not IsNumeric(xStart)) Or (xStart < 1) Then
MsgBox "Error entered, please enter again", vbInformation, "Kutools for Excel"
GoTo LInput
Else
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = xStart To xEnd
ActiveSheet.Range("A1").Value = " Company-00" & I
ActiveSheet.PrintOut
Next
ActiveSheet.Range("A1").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub
This comment was minimized by the moderator on the site
I need serial numbers like IA1-055242, IA1-055243, IA1-055244 .....
This comment was minimized by the moderator on the site
Thank you for posting this, it is very helpful. My question is this: I have 2 different barcodes that need to be incremented on one page, how can I modify the code to do that?
This comment was minimized by the moderator on the site
Sorry to ask this on a separate post... My serial numbers start with a ZERO, but when I run the program it eliminates the zeros. I tried to convert the number field to text, but that did not fix it. Other ideas?
This comment was minimized by the moderator on the site
R-Click Cell, Format, Custom, Where it says 'General', replace that with as many Zeros as your serial number will be. This will force the amount of zeros needed in front of your serial number. If I have a group of serial numbers that are 10 digit serials, I enter 0000000000 in the Type field to get '0004563571' to display in the serial number field.
This comment was minimized by the moderator on the site
Thank you Art. I did try that but the barcode kept eliminating the leading zeros... even after doing a custom number format.
This comment was minimized by the moderator on the site
my serial number start with 227861 how can i print from
This comment was minimized by the moderator on the site
printed like 30 copies but now i cant print, runned the script a lot of times but not working, dont do anything :(
This comment was minimized by the moderator on the site
thank you for the above, really helpful. is it possible to save and remember the last value
This comment was minimized by the moderator on the site
Hello, Pieter,
To save and remember the last printed value when you print next time, you should apply the following VBA code:

Sub IncrementPrint()
Dim xCount As Variant
Dim xScreen As Boolean
Dim I As Long
Dim xM As Long
Dim xMNWS As Worksheet
Dim xAWS As Worksheet
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
Set xAWS = ActiveSheet
On Error GoTo EMarkNumberSheet
Set xMNWS = Sheets("IncrementPrint_MarkNumberSheet")
EMarkNumberSheet:
If xMNWS Is Nothing Then
Set xMNWS = Application.Worksheets.Add(Type:=xlWorksheet)
xMNWS.Name = "IncrementPrint_MarkNumberSheet"
xMNWS.Range("A1").Value = 0
xM = 0
xMNWS.Visible = xlSheetVeryHidden
Else
xM = xMNWS.Range("A1").Value
End If
Application.ScreenUpdating = False
For I = 1 To xCount
xM = xM + 1
xAWS.Range("A1").Value = " Company-00" & xM
xAWS.PrintOut
Next
xMNWS.Range("A1").Value = xM
xAWS.Range("A1").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub

If you need to reset the printed number to the default number, please run the below code firstly, and then execute the above code to print.

Sub IncrementPrint_Reinstall()
Dim xMNWS As Worksheet
On Error GoTo EMarkNumberSheet
Set xMNWS = Sheets("IncrementPrint_MarkNumberSheet")
EMarkNumberSheet:
If Not xMNWS Is Nothing Then
Application.DisplayAlerts = False
xMNWS.Visible = xlSheetHidden
xMNWS.Delete
Application.DisplayAlerts = True
End If
End Sub
This comment was minimized by the moderator on the site
Hi, thank you for this code.. I have a question. I used this code but the series are jumping like 0071,0072,0073. happened like 3x between series 1-100. So i closed the vba without saving re install the code but it print the last series that was saved (0032). My question is how can I print continuously without the series jumping and how can I reprint again starting on 101? will really appreciate your answer. sorry for this. I am not a programmer, I hope you understand. Thank you! 
This comment was minimized by the moderator on the site
thank you for posting this, it is very helpful. My question is this: I have 2 different barcodes that need to be incremented on one page, how can I modify the code to do that?
This comment was minimized by the moderator on the site
Hello Desmond,
If you have 2 places on one page (like 2 Coupons or 2 templates / 2 vouchers etc.), then you can try using the below code. (Assuming your 1st barcode and 2nd barcode are in cells "A1" and "A20" of the same page, this code will increment values like Company-001 and Company-002 on first page and Company-003 and Company-004 on second page and so on. You can edit the cell no. and Company name as you want in lines 20, 21, 23, 24 and 28,29 of the code. 
It will also ask you to enter the starting number and ending number (Thanks to geniusman for this part of code). So for example your starting no. is 1 and ending no. 8, it will print 4 pages of 1,2 on 1st page, 3,4 on 2nd page, 5,6 on 3rd page and finally 7,8 on 4th page. Hope it helps you or anyone who is looking for this type of need/requirement. 
Modified Code:-----------------------------------------------------------Sub IncrementPrint()
'updateby Extendoffice
Dim xEnd As Variant
Dim xStart As Variant
Dim xScreen As Boolean
Dim I As Long
On Error Resume Next
LInput:
xStart = Application.InputBox("Please enter the first number:", "Kutools for Excel")
xEnd = Application.InputBox("Please enter the last number:", "Kutools for Excel")
If TypeName(xCount) = "Boolean" Then Exit Sub
If (xStart = "") Or (Not IsNumeric(xStart)) Or (xStart < 1) Then
MsgBox "Error entered, please enter again", vbInformation, "Kutools for Excel"
GoTo LInput
Else
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = xStart To xEnd
If I Mod 2 = 0 Then
ActiveSheet.Range("A1").Value = " Company-00" & I + 1
ActiveSheet.Range("A20").Value = " Company-00" & I
Else
ActiveSheet.Range("A20").Value = " Company-00" & I + 1
ActiveSheet.Range("A1").Value = " Company-00" & I
ActiveSheet.PrintOut
End If
Next
ActiveSheet.Range("A1").ClearContents
ActiveSheet.Range("A20").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub

---------------------------------------------------------------------------------------------------------Thanks,RNS
This comment was minimized by the moderator on the site
My cell is I3 and the number is 2298 when I try the (VBA code: Auto increment cell value after each printing:) it gives me 22981 how do I get it to 2298,2299,2300
This comment was minimized by the moderator on the site
Hi, jennifer,
To deal with your problem, please apply the following VBA code:
Note: Please change the prefix text and number to your own.

Sub IncrementPrint_Num()
Dim xCount As Variant
Dim xScreen As Boolean
Dim I As Long
Dim xStr As String
Dim xInt As Integer
On Error Resume Next
xStr = "Company-" 'prefix text
xInt = 2291 'number
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
xInt = xInt + 1
ActiveSheet.Range("A1").Value = xStr & xInt
ActiveSheet.PrintOut
Next
ActiveSheet.Range("A1").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Hello can you help me with this? I want the xINT to be more than 5 digits. Everytime i put a number with 6 digits, the count goes back to 1. How can i prevent that?
This comment was minimized by the moderator on the site
Hi,very interesting even though I'm looking for a different solution that I couldn't find and even if I tried to customize the code couldn't achieve so far.Following your example I would need to print the same page 100 times, into the same PDF for example and on each page the page number incremented.AS I said tried the collate method but as I understood it allows you to print together if you need multiple copies of the same printout.thanks in advance Giuseppe
This comment was minimized by the moderator on the site
Hi, this code is working excellent, but after 32767 cell value again it's going back to 1. After this value it's printing from number 1.
This comment was minimized by the moderator on the site
thank you very much, it works for me. And i manage to make a few minor change to suit my needs. Really Appreciate to your sharing.
This comment was minimized by the moderator on the site
Hi Jennifer,Try this
Sub IncrementPrint()
'updateby Extendoffice 20160530
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.PrintOut
ActiveSheet.Range("J18").Value = ActiveSheet.Range("J18").Value + 1
Next
'ActiveSheet.Range("J18").ClearContents'

Application.ScreenUpdating = xScreen
End If
End Sub
This comment was minimized by the moderator on the site
Thanks a million
This comment was minimized by the moderator on the site
Works good for printing incremental #'s. How do I print every 5th,10, when needed?
This comment was minimized by the moderator on the site
Is there a way to select what values I want to print? for example I printed sequence 1 to 30 but need to reprint sequence 15 to 19 again.
This comment was minimized by the moderator on the site
hey i want to change number of K11 cell number after print to 1-2-3-4-5-6 etc pls can u help ? and also tell me how to call that function pls help
This comment was minimized by the moderator on the site
I was wondering how to make a small change so that it prints 1 of 10, 2 of 10, 3 of 10, etc.
Otherwise this works great. Thanks.
This comment was minimized by the moderator on the site
hi, my name is suresh i have a data in excel format without any serial number which likes an example a way bill. i need to take it as 100 pages print and need to print the serial number what ever i need from a 4 digit but while printing i have to do in manual way. can u explain who to print auto generate the serial number code while printing
This comment was minimized by the moderator on the site
Great !! I am not a programmer but i managed to change the Cell ref and the uniq numbering i wanted. Worked superb for me God bless you!
This comment was minimized by the moderator on the site
Is it also possible to add to this code, so that 2 copies are automatically printed?
This comment was minimized by the moderator on the site
I would think you could change this part:ActiveSheet.Range("A1").Value = " Company-00" & I
ActiveSheet.PrintOut

to
ActiveSheet.Range("A1").Value = " Company-00" & I
ActiveSheet.PrintOut
ActiveSheet.PrintOut

to get 2 copies of each one.
This comment was minimized by the moderator on the site
I was wondering if you can just straight print out the file after reopening and it still follows the sequential number?
What I am currently doing is every time i open the file, ALT + F11 then F5 and indicate the number of copies . It will then print the file with the correct numbering, then just save again. and when I will reopen again, I just need to do same step.
If there's a code where you can just straight print it out every time you open the file and it will still follow the sequential numbering?
thanks in advance
This comment was minimized by the moderator on the site
I was wondering if you can just straight print out the file after reopening and it still follows the sequential number?

What I am currently doing is every time i open the file, ALT + F11 then F5 and indicate the number of copies . It will then print the file with the correct numbering, then just save again. and when I will reopen again, I just need to do same step.

If there's a code where you can just straight print it out every time you open the file and it will still follow the sequential numbering?

thanks in advance
This comment was minimized by the moderator on the site
Thankyou very much for sharing above code. It is very helpful for everyone. Can we add some code more for increasing 8 numbers instead of 1 after prints?Waiting for your reply. Thanks
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations