Salt la conținutul principal

 Cum se crește automat valoarea celulei după fiecare tipărire?

Presupunând că am o pagină de foaie de lucru necesară pentru a fi tipărită 100 de exemplare, celula A1 este numărul de verificare Company-001, acum, aș dori ca numărul să crească cu 1 după fiecare tipărire. Asta înseamnă că atunci când imprim cel de-al doilea exemplar, numărul va fi mărit automat la Compania-002, al treilea exemplar, numărul va fi Compania-003 ... o sută de exemplare, numărul va fi Compania-100. Există vreun truc pentru a rezolva această problemă în Excel rapid și posibil?

Creșteți automat valoarea celulei după fiecare tipărire cu cod VBA


săgeată albastru dreapta balon Creșteți automat valoarea celulei după fiecare tipărire cu cod VBA

În mod normal, nu există o modalitate directă de a rezolva această sarcină în Excel, dar, aici, voi crea un cod VBA pentru a face față acesteia.

1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Module Fereastră.

Cod VBA: creșterea automată a valorii celulei după fiecare tipărire:

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. Apoi apăsați F5 tasta pentru a rula acest cod și apare o casetă de prompt pentru a vă reaminti să introduceți numărul de copii pe care doriți să le imprimați foaia de lucru curentă, vedeți captura de ecran:

creșterea doc la imprimare 1

4. Clic OK butonul, iar foaia dvs. de lucru curentă se tipărește acum și, în același timp, foile de lucru tipărite sunt numerotate Compania-001, Compania-002, Compania-003 ... în celula A1, după cum aveți nevoie.

notițe: În codul de mai sus, celula A1 vor fi inserate numerele de ordine pe care le-ați comandat și valoarea inițială a celulei în A1 va fi șters. Și "Compania-00”Este numărul de ordine, le puteți schimba în funcție de nevoile dvs.

Cele mai bune instrumente de productivitate de birou

🤖 Kutools AI Aide: Revoluționați analiza datelor pe baza: Execuție inteligentă   |  Generați codul  |  Creați formule personalizate  |  Analizați datele și generați diagrame  |  Invocați funcțiile Kutools...
Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

Descriere


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
Comments (52)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I need serial numbers like IA1-01,03,05,07...........pls help me
This comment was minimized by the moderator on the site
How would I add code to print duplex on the VBA below. thanks in advance.

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("B2").Value = "0" & I
ActiveSheet.PrintOut
Next
ActiveSheet.Range("B2").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub

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
你好,如我要打印 由C001 - C010,但打卯出來後,第10份都變成 C0010, 請問如何解決
This comment was minimized by the moderator on the site
Hello, Tony,
To solve your problem, please apply the below VBA code:
Sub IncrementPrint_Num()
'Updateby Extendoffice
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 = 0   'start 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
If xInt < 10 Then
    ActiveSheet.Range("A1").Value = xStr & "00" & xInt
ElseIf xInt > 9 And xInt < 100 Then
    ActiveSheet.Range("A1").Value = xStr & "0" & xInt
Else
    ActiveSheet.Range("A1").Value = xStr & xInt
End If
ActiveSheet.PrintOut
Next
ActiveSheet.Range("A1").ClearContents
Application.ScreenUpdating = xScreen
End If
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hello,

Is there a way that you can code pressing the enter button after each number change?

Thank you in advance
This comment was minimized by the moderator on the site
Hello Ariane,

I am trying your requirement of 4 coupons or any no. of places to be incremented on same page and continue to next page. However in the meanwhile, if you have 2 coupons on one page then the below code might help you!

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
Hello RNS,Thank you for your share. If you have four coupons on the same page and continue to next page, please paste the following code in the Module Window.
Public Sub IncrementPrint()
'updateby Extendoffice
Dim resp As Variant, scr As Boolean, i As Long, j As Long

On Error Resume Next
resp = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
On Error GoTo 0

If resp = False Then Exit Sub
If resp < 1 Or resp > 100 Then
MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
Exit Sub
End If

scr = Application.ScreenUpdating
Application.ScreenUpdating = False
j = 0
For i = 1 To resp
ActiveSheet.Range("A1").Value2 = " Company-00" & i + 0 + j
ActiveSheet.Range("A2").Value2 = " Company-00" & i + 1 + j
ActiveSheet.Range("A3").Value2 = " Company-00" & i + 2 + j
ActiveSheet.Range("A4").Value2 = " Company-00" & i + 3 + j
ActiveSheet.PrintOut
j = j + 3
Next i
ActiveSheet.Range("A1,A2,A3,A4").ClearContents
Application.ScreenUpdating = scr
End Sub

For example, if you want to 2 copies, then the printed paper 1 will be Company-001,Company-002,Company-003,Company-004;and the the printed paper 2 will be Company-005,Company-006,Company-007,Company-008. Please have a try. Have a nice day.
Sincerely,Mandy
This comment was minimized by the moderator on the site
If I have 4 coupons per sheets, what do I have to modify on this code so the number will be incremented between the coupons on the same sheet as well as from every page it prints (i.e: page 1 has coupons # 1 to 4, page 2 has coupons from 5 to 8, etc.)
This comment was minimized by the moderator on the site
Hello Ariane,
Gald to help. If you have four coupons on the same page and continue to next page, please paste the following code in the Module Window.

Public Sub IncrementPrint()
'updateby Extendoffice
Dim resp As Variant, scr As Boolean, i As Long, j As Long

On Error Resume Next
resp = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
On Error GoTo 0

If resp = False Then Exit Sub
If resp < 1 Or resp > 100 Then
MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
Exit Sub
End If

scr = Application.ScreenUpdating
Application.ScreenUpdating = False
j = 0
For i = 1 To resp
ActiveSheet.Range("A1").Value2 = " Company-00" & i + 0 + j
ActiveSheet.Range("A2").Value2 = " Company-00" & i + 1 + j
ActiveSheet.Range("A3").Value2 = " Company-00" & i + 2 + j
ActiveSheet.Range("A4").Value2 = " Company-00" & i + 3 + j
ActiveSheet.PrintOut
j = j + 3
Next i
ActiveSheet.Range("A1,A2,A3,A4").ClearContents
Application.ScreenUpdating = scr
End Sub

For example, if you want to 2 copies, then the printed paper 1 will be Company-001,Company-002,Company-003,Company-004;and the the printed paper 2 will be Company-005,Company-006,Company-007,Company-008. Please have a try. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hello Ariane,
Please see my above post 0n 24-Feb-2022
Thanks,RNS
This comment was minimized by the moderator on the site
how can i count on from say number 779?  Thank you for sharing this code and any advice you can offer.
This comment was minimized by the moderator on the site
HIAfter doing the formula and selecting F5 I just get pop up Go to Print Area and then have to put in a reference  and I have tried everything but your pop up asking for how many prints does not come up? Helppppp please
This comment was minimized by the moderator on the site
press F5 in the VB window not the excel window.
This comment was minimized by the moderator on the site
God bless you and your soul man! you are a miracle :))
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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations