Preskoči na glavno vsebino

 Kako samodejno povečati vrednost celice po vsakem tiskanju?

Recimo, da imam stran delovnega lista, ki jo je treba natisniti v 100 izvodih, celica A1 je kontrolna številka Company-001, zdaj bi rad, da se število poveča za 1 po vsakem izpisu. To pomeni, da ko natisnem drugo kopijo, se številka samodejno poveča na Company-002, tretja kopija pa številka Company-003… sto kopija, številka bo Company-100. Ali obstaja kakšen trik za hitro in mogoče rešitev te težave v Excelu?

Samodejno priraste vrednost celice po vsakem tiskanju s kodo VBA

puščica modri desni mehurček Samodejno priraste vrednost celice po vsakem tiskanju s kodo VBA

Običajno te naloge v Excelu ni mogoče rešiti neposredno, toda tukaj bom ustvaril kodo VBA, ki bo obravnavala to težavo.

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Samodejno priraste vrednost celice po vsakem tiskanju:

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. Nato pritisnite F5 tipko za zagon te kode in pojavilo se bo pozivno polje, ki vas bo opomnilo, da vnesete število kopij, ki jih želite natisniti na trenutnem delovnem listu, glejte sliko zaslona:

povečanje dokumenta pri tiskanju 1

4. Kliknite OK in vaš trenutni delovni list se zdaj tiska, hkrati pa so natisnjeni delovni listi oštevilčeni Company-001, Company-002, Company-003… v celici A1, kot potrebujete.

Opombe: V zgornji kodi celica A1 bodo vstavljene zaporedne številke, ki ste jih naročili, in izvirna vrednost celice v A1 bo očiščeno. In “Podjetje-00"Je zaporedna številka, jih lahko spremenite glede na vaše potrebe.

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup  |   Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule   |  Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic   |  Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij:  12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (52)
No ratings yet. Be the first to rate!
MD KS SHADIN
MD KS SHADIN
This comment was minimized by the moderator on the site
I need serial numbers like IA1-01,03,05,07...........pls help me
Reply
0
0
erick
erick
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
Reply
0
0
Tony
Tony
This comment was minimized by the moderator on the site
你好，如我要打印 由C001 - C010，但打卯出來後，第10份都變成 C0010， 請問如何解決
Reply
0
0
skyyang
skyyang     Tony
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!
Reply
Report
0
0
Megan
Megan
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
Reply
0
0
RNS
RNS
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
Reply
Report
0
0
MandyZhou
MandyZhou     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
Reply
Report
0
0
Ariane
Ariane
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.)
Reply
0
0
MandyZhou
MandyZhou     Ariane
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
Reply
Report
0
0
RNS
RNS     Ariane
This comment was minimized by the moderator on the site
Hello Ariane,
Please see my above post 0n 24-Feb-2022
Thanks,RNS
Reply
Report
0
0
Kel
Kel
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.
Reply
0
0
Jennifer
Jennifer
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
Reply
0
0
f5
f5     Jennifer
This comment was minimized by the moderator on the site
press F5 in the VB window not the excel window.
Reply
0
0
someone
someone
This comment was minimized by the moderator on the site
God bless you and your soul man! you are a miracle :))
Reply
0
0
Shahzad
Shahzad
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
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations