How to create VBA code to increment number each time a copy is printed
Category: [Excel] Demo Available
How to create VBA code to increment number each time a copy is printed
Requirement:
The user has the following code which the user has tried to wrote that will automatically increment the Quote # each time a quote is printed.
The user has inserted this into my workbook.
Code:
Private Sub Workbook_BeforePrint1(Cancel As Boolean)
Cancel = True
Dim cnt As Long
Application.EnableEvents = False
With Sheets("QUOTE")
.Range("D3").Value = .Range("d3").Value + 1
.PrintOut copies:=1
Next
End With
Application.EnableEvents = True
End Sub
When Printing the Quote # is not being incremented by 1.
For eg 1000001 to change to 1000002 after printing, the third print 1000003 etc.
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148534-increment-number-each-time-a-copy-is-printed
Solution:
You had a 1 in the Sub name, also a "Next" without a "For".
Try this
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim cnt As Long
Application.EnableEvents = False
With Sheets("QUOTE")
.Range("D3").Value = .Range("d3").Value + 1
.PrintOut copies:=1
End With
Application.EnableEvents = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create code to calculate total length installed based on number of pages that are entered |
| How to remove the last X number of characters depending on the ending of the value |
| How to sort values in positive and negative numbers with formula |
| How to do a formula to compute IF A1 (number) is <1000 B1 = Bad |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.