Forum Discussion
Automatically inserting a sequential invoice number
The code that I posted will only run when you open the workbook.
As an alternative, remove the Workbook_Open code.
Then select Insert > Module in the Visual Basic Editor.
Copy the following code into the module:
Sub PrintInvoices() Dim i As Long Dim n As Long n = Val(InputBox("How many copies do you want to print?")) For i = 1 To n With Worksheets("Sheet1").Range("D1") .Value = .Value + 1 End With Worksheets("Sheet1").PrintOut End With End SubRunning this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.
If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.
The code that I posted will only run when you open the workbook.
As an alternative, remove the Workbook_Open code.
Then select Insert > Module in the Visual Basic Editor.
Copy the following code into the module:
Sub PrintInvoices()
Dim i As Long
Dim n As Long
n = Val(InputBox("How many copies do you want to print?"))
For i = 1 To n
With Worksheets("Sheet1").Range("D1")
.Value = .Value + 1
End With
Worksheets("Sheet1").PrintOut
End With
End SubRunning this macro will prompt you for the number of invoices to be printed. The invoice number will be incremented each time.
If you wish, you can assign the macro to a button on the sheet, or to a Quick Access Toolbar, or to a keyboard shortcut.
HansVogelaar This worked when I did the one where it changed every time we open the workbook but then I saw your code for printing copies in sequential order so I tried it but it isn't working for me, the printing option to print multiple copies of the invoice with sequential numbers is not coming up for me... what am I doing wrong?
Which macro is failing for you?
- Honestly I am not sure! When I did the code you posted Aug 12 2022 06:44 AM it worked perfectly.
Then I was told they want to print like 50 copies at one and have the invoice number change in sequence as it prints so I tried your next code you posted Aug 12 2022 12:24 PM saved as enable macros file and when I go back in to print a few to test it it doesn't prompt me for the amount of invoices to print. And obviously if I print anyway it doesn't work, just prints whatever if on the current page. Any ideas?
I am also trying to get an invoice template to have sequential numbering to add 1 each time it is opened from a template. I am using Office for Mac and have tried using the instructions you have and I continue to get an error.
- I keep getting "compile error: End With without With" can you help?
Sorry about that, it was air code.
Sub PrintInvoices() Dim i As Long Dim n As Long n = Val(InputBox("How many copies do you want to print?")) For i = 1 To n With Worksheets("Sheet1").Range("D1") .Value = .Value + 1 End With Worksheets("Sheet1").PrintOut Next i End SubHansVogelaar If I may ask, I have used your code and the results are good, but I would like to ask if I can change the number format starting from 0001 and incrementing by 1 each time I print.
- This is exactly what I was looking for....... Thank you so much!
