How to create auto numbering invoice templates

Occasional Visitor

I am struggling to create an excel template for a client. I need it to automatically generate a new invoice number each time my client opens the document. The numbers need to be sequential and I would like to define the starting number. I have tried using this visual basic:


Private Sub Workbook_Open()
Range(“C2”).Value = Range(“C2”).Value + 1
End Sub


Where C2 is the cell the number needs to be automatically generated to but I keep getting a runtime error (see file here).


Can anyone help? I am using Office 365.


Thank you in advance...


1 Reply

@Jane_Chameleon I think you're getting an error because you have an undefined sheet for that range.  Try defining the sheet before that RANGE function.

That said, your technique has a number of drawbacks including if they open it up and close it without 'creating' a new invoice the number increments, if they open an old invoice to look at it the number increments, if they open the template and create and print a new invoice but don't save it (or it fails to save) then the increment doesn't save and you get duplicate invoice numbers.

I have a similar system for POs and such but I base it off a LOG.  My LOG has a button to create a new item that will identify the new number  and pop up a form with basic info to populate the LOG.  That way the LOG will show each number and relevant info and then it will also open the template and pre-populate those values into the sheet for the user and even saves that new sheet in a given location with a fixed naming style so the folder shows all the items in a unified way. - works really well, but that is just me.