Forum Discussion
Solved
Automatically inserting a sequential invoice number
Hello everyone, Currently I pay a company to print blank invoices with sequential numbers. I am attempting to find a way to print a similar blank invoice (that I have created in excel) but on each ...
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.
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.
You can apply the custom number format 0000 to the cell with the invoice number.
Enter 1 in this cell.
- Thank you very much, I have tried what you suggested. The results are excellent.
Is there a way to create a save as button that will save the filename as the invoice number? HansVogelaar
Using a variation of the code in my previous reply (invoice number in D1 on Sheet1), you could run the following macro from another workbook. Just make sure the invoice workbook is the active workbook when you run the macro.
Sub SaveInvoices() Dim i As Long Dim n As Long Dim p As String n = Val(InputBox("How many copies do you want to print?")) p = ActiveWorkbook.Path If Right(p, 1) <> Application.PathSeparator Then p = p & Application.PathSeparator End If For i = 1 To n With Worksheets("Sheet1").Range("D1") .Value = .Value + 1 ActiveWorkbook.SaveCopyAs p & "Invoice " & .Value & ".xlsx" End With Next i End Sub
- I just tested it and it works perfectly, thanks so much for your help!
