SOLVED

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...

 

5 Replies

@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.

Hi

 

Will someone please share an excel invoice document with auto numbersing

@JohnnyP51  As I noted I created a Log that creates unique purchase requisition docs.  I am attaching it here because it can be easily modified for most any purpose and include the specific fields and requirements of what you need.  I'm no accountant so I don't know what fields are needed for invoices and if that would be standard across the industry (which I doubt).  I hope this helps.

This does require macros so they must be turned on and allowed for this file.  Also it assumes a 'standard' file system so the file is on that computer or mapped network drive (i.e. not set up for use on OneDrive but I have done that with other versions).  The 'blankPR' file must be in the same folder as the log and then it will create subfolders based on the year.  This is set up to create numbers in the format of YYYY-nnnn  where YYYY is the present year and then nnnn is unique numbers counting from 0001 up for that year.  Again, that could also be customized.  

@mtarler 

 

Thank You

Much appreciated

JohnnyP

best response confirmed by mtarler (Trusted Contributor)
Solution

@JohnnyP51 That all said, I'm thinking of a very simple macro similar to the one posted in the original post above that could work and address my concerns with numbering. Basically on open increment the number in the template (similar to above) but only if it is the Blank Template, then a) save the template and then b) use Save AS to save the template as a new file based on the invoice number and make a small change so it knows it is no longer the template (i.e. now someone can open this document and review/modify/correct it as needed without generating new numbers). 

Attached is a sample.  when you open it if you allow macros it will do as I noted above.  If you disable macros you can edit it and save/update the template.  If you trouble just use any created output file, make the changes you want, then type "TEMPLATE" into cell B2.  Note cell locations B2 and C3 as the invoice number are hard coded into the simple macro so if you want to move them you will have to tweak the macro itself.