Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Automatically inserting a sequential invoice number

Copper Contributor

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 copy i would like the invoice number to increase by one.  Is this possible to do without manually changing everyone? 

I do not totally understand the concept of Macro's, but I have a basic knowledge on how to attach one to my system if necessary 

15 Replies

@AJ-FLAME 

Let's say you want the invoice number in cell D1 on Sheet1.

 

Press Alt + F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook under Microsoft Excel Objects in the Prpject Explorer pane on the left.

Copy the following code into ThisWorkbook:

Private Sub Workbook_Open()
    With Worksheets("Sheet1").Range("D1")
        .Value = .Value + 1
    End With
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

You'll have to allow macros when you open it.

 

Each time you open the workbook, the invoice number will be increased by 1.

if I print multiple copies will each copy have a new number? or will the number only change when i reopen the file?
best response confirmed by AJ-FLAME (Copper Contributor)
Solution

@AJ-FLAME 

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 Sub

Running 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.

This is exactly what I was looking for....... Thank you so much!
I keep getting "compile error: End With without With" can you help?

@AJ-FLAME 

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 Sub
I just tested it and it works perfectly, thanks so much for your help!

Is there a way to create a save as button that will save the filename as the invoice number? @Hans Vogelaar 

@pauljjk 

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

@Hans Vogelaar 

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.  

itlegalrn_0-1705089777985.png

 

@itlegalrn 

Please click the Debug button in the error message.

Which line of code is highlighted?

@Hans Vogelaar  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?

@mander1281 

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?

@mander1281 

If you run the macro

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 Sub

you should see a prompt:

HansVogelaar_0-1705445870216.png

The code will then increment the number in cell D1 on Sheet1 and print that sheet.

If you want to use another sheet or another cell, adjust the code accordingly.

1 best response

Accepted Solutions
best response confirmed by AJ-FLAME (Copper Contributor)
Solution

@AJ-FLAME 

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 Sub

Running 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.

View solution in original post