MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create a Unique Record Number


November 09, 2001 - by Bill Jelen

Janet from England posed today's question.

We need to know how to set up a macro which will assign a unique invoice number, how to set up a second macro which will save the completed invoice as the unique number.

Here is the hypothetical application that I am imagining. There is a workbook with an Invoice worksheet and a Menu worksheet. I propose storing the last invoice number on an out of the way cell on the Menu worksheet, lets say Z1. If the invoice number is supposed to go in cell H2 of the invoice worksheet, then this macro would do the trick:

Sub GetNextInvoiceNumber()
    LastInv = Worksheets("Menu").Range("Z1").Value
    NextInv = LastInv + 1
    Worksheets("Invoice").Range("H2").value = NextInv
    Worksheets("Menu").Range("Z1").value = NextInv
End Sub

Then, to save the Invoice, something like this:

Sub SaveInvWithNewName()
    ' Copy Invoice to a new workbook

    Worksheets("Invoice").Copy
    NewFN = Range("H2").Value & ".xls"
    ActiveWorkbook.SaveAs NewFN
    ActiveWorkbook.Close
End Sub

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.