Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 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


Print odd, even, current, or specified pages of a worksheet

Kutools for Excel's Print Specified Pages utility can help you to quickly print odd, even, current or specified pages as you need.

doc increment when printting 2

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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


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.
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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    TBOne · 5 months ago
    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.
  • To post as a guest, your comment is unpublished.
    unknown · 8 months ago
    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
  • To post as a guest, your comment is unpublished.
    Ib Alsa · 9 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Bill Turek · 9 months ago
    Works good for printing incremental #'s. How do I print every 5th,10, when needed?
  • To post as a guest, your comment is unpublished.
    Arash · 11 months ago
    Thanks a million
  • To post as a guest, your comment is unpublished.
    jennifer · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      tan chee ho · 10 months ago
      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.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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!
      • To post as a guest, your comment is unpublished.
        Richard · 10 months ago
        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?
  • To post as a guest, your comment is unpublished.
    Desmond · 1 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Pieter · 1 years ago
    thank you for the above, really helpful. is it possible to save and remember the last value
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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
  • To post as a guest, your comment is unpublished.
    Sniper · 1 years ago
    printed like 30 copies but now i cant print, runned the script a lot of times but not working, dont do anything :(
  • To post as a guest, your comment is unpublished.
    romik · 1 years ago
    my serial number start with 227861 how can i print from
  • To post as a guest, your comment is unpublished.
    Kris · 2 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Art · 2 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Kris · 1 years ago
        Thank you Art. I did try that but the barcode kept eliminating the leading zeros... even after doing a custom number format.
  • To post as a guest, your comment is unpublished.
    Kris · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Abdul · 2 years ago
    I need serial numbers like IA1-055242, IA1-055243, IA1-055244 .....
  • To post as a guest, your comment is unpublished.
    Kerry · 2 years ago
    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 :-)
    • To post as a guest, your comment is unpublished.
      Valentas · 2 years ago
      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.