If you need to create invoices for your business, you can use one of the many invoice templates found on Office.com. Although these templates are a great starting point, they won't automatically generate unique invoice numbers—you'll need a macro for that.

Excel MVP Bill Jelen graciously offered up a video that demonstrates how to create just such a macro, along with a number of other useful tips. Thank you, Bill!

What's in the video

Here's a breakdown of the overall proccess:

  1. 0:15: Find and open an invoice template in Excel.
  2. 1:03: Make whatever customizations you like, and then save the invoice template in a file format (.xslm) that is macro-friendly.
  3. 1:26: Create a macro that automatically 1) increments the invoice number and 2) clears cells on the worksheet so you can start fresh on the next invoice.
  4. 3:00: Attach your macro to a shape, so that you can run it easily from the invoice worksheet.
  5. 3:44: Create another macro that lets you save a copy of the invoice as a macro-free file (.xslx) with a unique file name.
  6. 4:33: Attach that second macro to a different shape in the workbook.
  7. 4:58: Save the master version of the workbook as a macro-enabled (.xslm) file.

The macro code

If you want to try this yourself, feel free to reuse Bill's VBA code for the two macros. 

If you've never used macros before, your copy of Excel may have macros turned off, even for .xlsm file types. To check, use the keyboard sequence ALT+T, M, S to get to macro settings in the Trust Center dialog box. If Disable All Macros without Notification is selected, choose Disable All Macros with Notification instead. If you use this option, Excel prompts you to enable macros each time you open a file that has them.

Macro #1: Generate the next invoice number

Sub NextInvoice()
    Range("E5").Value = Range("E5").Value + 1
    Range("A20:E39").ClearContents
End Sub

Macro #2: Save invoice with new name

Sub SaveInvWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\aaa\Inv" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

For more great tips, visit Bill's site, MrExcel.com. Also, if you're new to the concept of VBA and macros, and you'd like to explore further, check out Get started with VBA in Excel 2010 or Create or delete a macro. 

 -- Anneliese Wirth