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