Ir para o conteúdo principal

 Como incrementar automaticamente o valor da célula após cada impressão?

Supondo que eu tenha uma folha de trabalho necessária para imprimir 100 cópias, a célula A1 é o número do cheque Empresa-001, agora, gostaria que o número aumentasse em 1 após cada impressão. Isso significa que quando eu imprimir a segunda cópia, o número será aumentado para Empresa-002 automaticamente, a terceira cópia, o número será Empresa-003 ... cem cópias, o número será Empresa-100. Existe algum truque para resolver esse problema no Excel de forma rápida e possível?

Valor de célula de incremento automático após cada impressão com código VBA

seta azul bolha direita Valor de célula de incremento automático após cada impressão com código VBA

Normalmente, não existe uma maneira direta de você resolver essa tarefa no Excel, mas, aqui, vou criar um código VBA para lidar com isso.

1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: valor de célula de incremento automático após cada impressão:

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. Então aperte F5 para executar este código, e uma caixa de prompt é exibida para lembrá-lo de inserir o número de cópias que deseja imprimir na planilha atual, consulte a captura de tela:

incremento doc ao imprimir 1

4. Clique OK e sua planilha atual está sendo impressa agora e, ao mesmo tempo, as planilhas impressas são numeradas Company-001, Company-002, Company-003 ... na célula A1 conforme necessário.

Note: No código acima, a célula A1 serão inseridos os números de sequência que você solicitou e o valor da célula original em A1 será apagado. E “Empresa-00”É o número de sequência, você pode alterá-los conforme sua necessidade.

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios  |   VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula   |  Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células   |  Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas:  12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição

Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
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