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.
![]()
Use the ROW function to number rows. In the first cell of the range that you want to number, type =ROW(A1). The ROW function returns the number of the row that you reference. For example, =ROW(A1) returns the number 1. Drag the fill handle across the range that you want to fill.
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:
![]()
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 Tabctl32 ocx download visual basic.
How To Generate Random Serial Numbers In Excel
Sub SaveInvWithNewName()
Dim NewFN As Variant ‘ Copy Invoice to a new workbook ActiveSheet.Copy NewFN = “C:aaaInv” & Range(“E5”).Value & “.xlsx” ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub You Serial Numbers
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.
How To Generate Serial Numbers
— Anneliese Wirth
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |