提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在每次打印后自动递增单元格值?

假设,我有一个工作表页面需要打印100副本,单元格A1是支票号码Company-001,现在,我希望每打印一次后,数字将增加1。 这意味着当我打印第二个副本时,该号码将自动增加到Company-002,第三个副本号码将为Company-003 ...一百个副本,号码将为Company-100。 有没有什么技巧可以在Excel中快速解决这个问题?

使用VBA代码进行每次打印后,自动递增单元格值


打印工作表中的奇数,偶数,当前或指定页面

Kutools for Excel's 打印指定的页面 实用程序可以帮助您根据需要快速打印奇数,偶数,当前或指定的页面。

打印2时doc增量

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 使用VBA代码进行每次打印后,自动递增单元格值


通常,在Excel中没有直接的方法来解决这个任务,但是,在这里,我将创建一个VBA代码来处理它。

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:每次打印后自动递增单元格值:

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。 然后按 F5 键以运行此代码,弹出提示框提醒您输入要打印当前工作表的副本数量,请参阅屏幕截图:

打印1时doc增量

4。 点击 OK 按钮,并且您当前的工作表正在打印,同时,打印的工作表根据需要在单元格A001中编号为Company-002,Company-003,Company-1 ...。

注意:在上面的代码中,单元格 A1 将插入您订购的序列号和原始单元格值 A1 将被清除。 而“公司00“是序列号,您可以根据需要更改它们。


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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 · 2 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 · 5 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 · 6 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 · 6 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 · 8 months ago
    Thanks a million
  • To post as a guest, your comment is unpublished.
    jennifer · 9 months 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 · 7 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 · 9 months 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 · 7 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 · 10 months 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 · 11 months 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 · 11 months 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 · 1 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 · 1 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 · 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.
    Abdul · 1 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 · 1 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.