Generate invoice numbers in Excel

AppId is over the quota
AppId is over the quota

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!

Here's a breakdown of the overall proccess:

0:15: Find and open an invoice template in Excel.1:03: Make whatever customizations you like, and then save the invoice template in a file format (.xslm) that is macro-friendly.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.3:00: Attach your macro to a shape, so that you can run it easily from the invoice worksheet. 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.4:33: Attach that second macro to a different shape in the workbook.4:58: Save the master version of the workbook as a macro-enabled (.xslm) file.

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 open 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 will prompt 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 VBA and want to explore the topic, check out Get started with VBA in Excel 2010 or Create or delete a macro. 

 -- Anneliese Wirth 

View the original article here