Auto Generate Invoice Number in Excel Template (4 Quick Steps)

Suppose we invoice a certain company repeatedly using an Excel Invoice Template. We want an auto generated invoice number every time we open the Template Workbook to generate a new Invoice for the customer.

In the Invoice Template below, in addition to auto-generating a new Invoice Number, we also want to clear the Item Description cells (B8:G10), while leaving the Bill To company credentials intact.

In this article, we’ll demonstrate how to use a VBA Macro to do this.

Step 1 – Selecting an Invoice Template in Excel

Making invoices can be done in three ways; Manually, Automatically by using Templates in Excel, or using third party Financial software. We’ll use Excel’s free Templates here.

Go to File > New.
Enter Invoice in the Search bar.

Excel offers numerous Templates to choose from.

Choose one of the Templates (here, Blue Invoice) and click on it.

Excel displays a Create button.

Click on it to download and be able to use the Template.

Step 2 – Saving Invoice Template in XLSM File Format

Excel by default saves files in XLSX formats. Since we are going to use VBA Macro to auto generate invoice numbers, we have to save the XLSX file in XLSM format instead.

Go to File > Save As.
Select a convenient location and assign a name to the downloaded template.
Select Excel Macro-Enabled Workbook as Save as type.
Click Save.

Step 3 – Inserting Macro to Auto Generate Invoice Number

Use ALT+F11 to open the Microsoft Visual Basic window.
Double-click on ThisWorkbook under VBAProject.

A Workbook Open code window opens.

Copy and paste the following macro in the window:

Private Sub Workbook_Open()
Range("G4").Value = Range("G4").Value + 1
Range("B8:G10").ClearContents
End Sub

The macro is a Private macro. Every time you open the xlsm file, the macro adds 1 to auto generate the invoice number in cell G4. It also clears the Item Description contents.

Step 4 – Auto Generated Invoice

After inserting the macro, exit from Excel and reopen the Excel File (here named Auto Generate Invoice Number).

Excel creates a new Invoice by adding 1 to the Invoice Number (now Invoice # 1001), and clearing all the Item Descriptions. All other fields and information are intact.