Serial Numbers

[Mike Bellamy]

Hi,

How can I add a unique serial number to my excel spreadsheet? I have made a document transmittal template and I want a transmittal number on it that will roll over to a new number every time the template is saved (as an excel xls or xlsx file). I'm hoping there is a built in feature that I can use but haven't been able to find it so far.

Any help would be appreciated.

Thanks,

Mike Bellamy

 

[jasonb75]

Welcome to the board!

Unfortunately no built in feature, also when using "Save as", your original workbook is usually closed without save, so the increment would reset (there are exceptions depending on how you save as, but this would apply to normal use).

Maybe your better option would be to use a "workbook open" event to deal with it.
By adding the code below to the "this workbook" module of your file (please test on a COPY first, not the original), it will increase the serial number by 1 when the file is opened, and save at the same time. The serial number would need to be located in a worksheet cell for this to work.

Sub workbook_open()
Sheets("Mysheet").Range("A1").Value = Sheets("Mysheet").Range("A1").Value + 1
ActiveWorkbook.Save
End Sub

You will need to change "Mysheet" to the name of the worksheet with the serial number, and "A1" to the containing cell.

Haven't tested the code, it should do what you need, but please, test it on a copy file first.

Hope this helps

Jason