Free Microsoft Excel 2013 Quick Reference

Invoice Number Increment

Hi folks,

I am in the process of creating an invoice in excel. I want it to do all the obvious things an invoice should do - like increment the invoice number, clear specific cells and save data after each save.

I have been searching the forums for help regarding the above mentioned functions. I have read a few posts that provide some code to achieve such outcomes but my problem, being a complete excel beginner, is that even being provided with the code, I do not know how to implement it in my workbook. If someone could help me (step by step) implement these (macros?) I would be most grateful.

So far I have finished my layout and have some very basic summing functions working. I should point out that I would like the invoice number not to start at zero (new excel invoice will hopefully replace hand written type).

I hope this is clear enough for everyone.

Best regards,

Post your answer or comment

comments powered by Disqus
I have set up an invoice in excel , each time I load the date changes using =TODAY()
how do I get the invoice number to change as well , so that each time I access the document the invoice number increases by 1


I have created an Invoice template using excel 2007.

The actual "Invoice Number" is stored in a cell (K 3)

I am looking for a VBA procedure or function which runs when the spreadsheet opens.

On opening the spreadsheet the following events could occur

1. Cell K 3 (the invoice number) is incremented by one.

2. The (template) spreadsheet is then saved (with the invoice number incremented so that next time the template is instantiated, the invoice number will be one higher)

3. The spreadsheet then calls a (save as) function and saves itself in an "invoices" directory eg: "x:invoices"
with the filename Invoice12345.xls (where in this example, 12345 is the newly incremented invoice number)

I know a little bit of VBA, but I am an Excel newbie and was hoping someone could help.
Thanks in advance for anyones help with this!

Hello Everyone,

I have created a templet for an invoice, but I cannot get the invoice number to increment. I am using the following code from a previous thread

    Sheets("Sales Invoice"). Range("B7").Value = Sheets("Sales Invoice").Range("B&").Value + 1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The address of the invoice number is B7 and the name of the sheet where the B7 is located is Sales Invoice.

I want to start the invoice number at 100000 and add the letters PM to the beginning so that when it is displayed and printed out it reads PM100000.

Then I want it to increment by 1 and this number be placed in the B7 area when the invoice is opened next time.

Thanks in advance to any and all help!!

Rhonda ... a newbie who wants to learn

Hello everyone, I have just found this site and hope that I can offer a little bit of my own knowledge in return for some help I need at the moment.

I am setting up an Invoice template for my brother's small business and need to auto increment an invoice number each time the template is opened to be used. I have done this in Word using a Macro and a registry key but would like to also do this in Excel. I have found some code via Google - which is outlined below that I have included in my worksheet as a macro, but I am not sure where to set up the registry values sAPPLICATION, sSECTION and sKEY.

    Const sAPPLICATION As String = "Excel" 
    Const sSECTION As String = "Invoice" 
    Const sKEY As String = "Invoice_key" 
    Const nDEFAULT As Long = 1& 
    Dim nNumber As Long 
    With ThisWorkbook.Sheets("Invoice") 
        With .Range("D5") 
            If IsEmpty(.Value) Then 
                .Value = Date 
                .NumberFormat = "dd mmm yyyy" 
            End If 
        End With 
        With .Range("E5") 
            If IsEmpty(.Value) Then 
                nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) 
                .NumberFormat = "@" 
                .Value = Format(nNumber, "0000") 
                SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& 
            End If 
        End With 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone help me create the required registry key to get this to work? I'll need exact steps as my registry experience is pretty limited to typing 'regedit'!
Thanks in advance,

Hi there,

Some time ago I had an invoice template that would auto-increment the
invoice number each time it was opened to save the time & trouble of adding
the details manually.

I seem to have 'lost' it - could some kind soul please point me in the right
direction to reinstate this useful feature?

Was it a standard template that came with Office?

Would this same idea work across all versions of Exel?

Thanks & regards,

I find all this very confusing - I am not that computer literate. How do I
save the last invoice number to a cell - will it automatically change with
each new invoice or will I have to type it in each time?

" wrote:

> Depending on the complexity of use required, you can save
> last invoice number to a cell (or add it to a list of
> numbers), then simply add the increment to that number for
> the new invoice.
> >-----Original Message-----
> >I have an Invoice template, which is used with SaveAs to
> create a new invoice. Can the invoice numbers be
> automatically incremented?
> >--
> >Les
> >.
> >

Main Invoice1.xlsxHello,

I am trying to build an invoice template with incrementing invoice numbers. I was able to find the proper code on the site to make that work, but I now running into new problems which i was unable to find the code for.

I want to make changes to the template and then save it as a new file and have the template stay as it was when I first opened it, but for it to have a new invoice number when I reopen it for a new invoice. I am also having a problem with not having the code carry over to a new excel sheet after I have saved it.

I hope I am making sense and I apologize for asking a question that might have already been answered as I was unable to locate it. Below is the code and the file is attached.

     'Change the sheet and cell reference to where the invoice number is located
    With Sheets("Invoice").Range("M3") 
        .Value = .Value + 1 
    End With 
End Sub 
Private Sub Workbook_close() 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I need a simple Invoice that automatically increments the invoice number by one...I have found a few answers about codes in text files etc. but I have not a clue how to do any of this....Does anyone have such an invoice template handy ?

I created an invoice and saved it as a template. I want the invoice number to increment by 1 each time the template is opened to create a new invoice. How can I make this work?

I am trying to get my invoice to increase in numbers incrementally but I have
not been able to find the method used. Any suggestions?

i need to create a button that when i press it it'll print and save invoices in increments (example invoice_001, invoice_002) and if i save without printing my invoice will increment....i also need it to behave where if i do not edit a blank invoice the number does not increment. So if i cancel it does not increment the next time. can someone help me.

Hi there,

Some time ago I had an invoice template that would auto-increment the
invoice number each time it was opened to save the time & trouble of adding
the details manually.

I seem to have 'lost' it - could some kind soul please point me in the right
direction to reinstate this useful feature?

Was it a standard template that came with Office?

Would this same idea work across all versions of Exel?

Thanks & regards,

I want to make an invoice template and have others pull up the template and each time they do have it add one to the invoice number. Then I want them to populate the invoice and save it under a different name. My problem is when they open the file under the different name it adds one to the invoice as well. How can I make it so the saved copy invoice number is locked? Also if I wanted to start at a number say 4000 how would I accomplish this. The code in my work book is as follows: I am a novice at VBA and macros.

    Const sDEFAULT_PATH As String = "C:Documents and Settings....." 
    Const sDEFAULT_FNAME As String = "C:Documents and Settings.....txt" 
    Dim nFileNumber As Long 
    nFileNumber = FreeFile 
    If sFileName = "" Then sFileName = sDEFAULT_FNAME 
    If InStr(sFileName, Application.PathSeparator) = 0 Then _ 
    sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName 
    If nSeqNumber = -1& Then 
        If Dir(sFileName)  "" Then 
            Open sFileName For Input As nFileNumber 
            Input #nFileNumber, nSeqNumber 
            nSeqNumber = nSeqNumber + 1& 
            Close nFileNumber 
            nSeqNumber = 1& 
        End If 
    End If 
    On Error Goto PathError 
    Open sFileName For Output As nFileNumber 
    On Error Goto 0 
    Print #nFileNumber, nSeqNumber 
    Close nFileNumber 
    NextSeqNumber = nSeqNumber 
    Exit Function 
    NextSeqNumber = -1& 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you in advance for any assistance. I have been trying to follow some previous threads (mainly the Thread:
Auto Increment Invoice Number When File Opened

and Thread:
Automatically Increment Invoice Number.

Unfortunately, I am lost.

How do I make these posts relevant to my own workbook?
I am trying to store the invoice number in a range of cells (I have merged cells together to keep the template orderly). The range of cells is AH2:BH2.

Hello Gurus,

I have figured out a simple way of creating sequential invoice numbers by creating a macro which simply copies the value of "1" & paste special/values/add this to existing number ie 1000 creating 1001 which is then "paste special" into the destination cell on invoice.

This creates a rolling shedule ie next time it becomes 1002 etc.

This works fine except that this "source" numbers register needs to be located in a separate spread sheet to the destination to make sure this system is not corruptible. The reason for this is I may be saving several copies of the invoicing spreadsheet as different client job's in progress, & I don't want to end up with double up invoice or quote numbers, hence for my pupose, a separate invoice number source or "register" if you like (in the form of a separate work book) is important.

I have discovered I can still run the macro looking to this separate sheet to source the sequential number & it works perfectly, but only while the source work book is open.
Can I make this work without having to open the source work book every time? Or alternatively can I tell the source workbook to automatically open when I open the destination (invoice) work book?

Many thanks for your help in advance, FYI you will have to be fairly specific & use laymans terms! Thanks, your experience will be put to good use!

Good Day!

I have a spreadsheet/file that is used as an invoice (will be attached below). Where the BLACK square is (Q25) we need to have an invoice number that will increase by one each time it's opened. For example, if it's currently LUNV01-1, once it's saved and closed, the next time it's opened, it should say LUNV01-2, etc.

And while I'm 'at it', will it matter if this is saved as a 'save as...' and he saves it by another name, should it be made a 'template' and then have him 'save as...'? The reason I'm asking is so that it doesn't get 'ruined' if it's saved wrong, etc.

Thanks so much!

BTW, I should advise you that I'm a total neophyte when it comes to creating macros and working in VBA (I'm using Excel 2007, but my friend is using a previous version which is why it's in 'compatibility mode'.) So if I need to do this, please explain where I need to 'go' and what I need to do... thx -- OH... and the macros that he already has come up as being turned off, but they do re-enable -- however, I had to delete all pages behind this front one to make the file small enough to attach.. but they're not relevant to the invoice numbering issue. :>)


Hi - this is real newbie question. I have been searching this forum and reading the answers that have been given to others concerning having the invoice number automatically update when an invoice is saved. I have seen many times where people have referred to an Excel Invoice Template that is on here somewhere. Where is it? I cannot find it anywhere!

What I need to do is I have created an invoice in a workbook, and I have created a macro to save relevant info (Date, custname, etc) to another worksheet called 'Summary'. I have put an invoice column on this and have put '1' in the first cell. I thought I could use this formula:
=MAX(Summary Sheet).Range+1.

It's not working! I have been playing with this for hours, and I did have something working last night, but whenever I ran my save macro, it would just write over what was in the Invoice '1' row. Could anyone help me to create a formula that will let me do this.

I have been looking for an invoice template so I could see how the formula was written in the invoice section for that.

Thank you for your time in reading this essay!


Hi All,

Firstly thanks for taking the time to look at this post.

The problem I have is relating to invoices. I have an Excel spreadsheet which I write down all payments I make for invoices. The invoice numbers are always a number and started at 1 and then increment by 1. IE 1, 2,3 etc.

What I would like to do is to have a way in which I could look in my sheet to see if I have any invoices missing. ie 6 and 8 is there but 7 is not.

Due to the nature of invoices they are not always in order so I might pay 9 before I pay 8 and therefore on my spreadsheet they wont be in order. they are currently in order of when I paid them.

Would anyone know a way I could do this?

Many tnaks again

Sorry I shouldn't of posted before searching...

I now found this code

Sub INGsek()

Set rngStart = Range("A1")
Set rngResult = Range("B1")

Set rngSearch = Range(rngStart, rngStart.End(xlDown))
j = 0
i = 1

For Each cl In rngSearch
If i > 1 Then
diff = rngSearch(i) - rngSearch(i - 1)
If diff > 1 Then
For k = 2 To diff
rngResult.Offset(j, 0) = rngSearch(i - 1) + k - 1
j = j + 1
End If
End If
i = i + 1
End Sub

Which works a treat but how can I order the inovices by invoice number without actuall reordering the sheet?

This is because this code only looks a the next value and not 2 ahead. So if I have 1, 3 , 2 in that order it will report that 2 is missing


I have an invoice sheet and when one job is entered into it I would like a new invoice sheet with the same format to appear while the previous invoice is filed away possibly in another workbook. The jnvoices are all numbered so I'm also attempting to have the invoice number grow by 1 everytime a new automatic invoice is created. Any ideas attached is the invoice for further analysis.

I need some help auto incrementing my invoices.
The way I do my invoicing is like this. I have 1 Invoice in a worksheet (eg April 1) When I make my invoice for the next day I right click on the April 1 tab at the bottom. Right click/Move copy/move to end &check the create a copy box. It changes to april1(2)I then right click and change the date to the next invoice date. This might seem goofy, but it works for me. I have my Invoice number at the top of the page in cell e2. Is there a formula to automatically increase the invoice number by 1 every time I copy it? Would appreciate any help. Thanks Ben

First of all I am not very good with macros and most of what I have done so far has been with the help of you people so thanks for that.I have a invoice workbook that I start with one invoice and a data sheet that the invoice draws information from the data sheet to populate the invoice I have a macro button that copies invoice to the front of the workbook and increments invoice number by one , clears contents in specific cells .I have a data validation list box in the cell where the invoice number goes(l8) that will only let invoice numbers be entered from a specific named range, which I have on my data sheet. Sometimes there might be a invoice in the middle of the workbook that I would like to copy and move to the start of the workbook because of the information that might be on it, but when I do this now it copies in front of the active sheet and increments the number by one and I would end up with a duplicate invoice number. I would either like to change the macro so that it warns me if there is duplicate invoice numbers in the workbook or change it so it will copy from anywhere in the workbook and increment invoice number by one higher than the first sheet.I hope I have explained this clear enough.I would attach sheet but workbook is to large.I will attach the macro though. Any help would be appreciated. Thanks in advance
Option Explicit
Sub test()
    Dim ThisSht As Worksheet
    Dim l As Long
    Set ThisSht = ActiveSheet
    With ThisSht
        l = .Range("l8")
        .Copy After:=ThisSht
        l = l + 1
        .Range("l8").Value = l
    End With
End Sub

Hello all. I'm designing an invoice, and I want the invoice number in my template, let's say 11050, to increase when the template is opened and the Save As function is used. So when I reopen the template, it will read 11051.

Here's the code I have:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI Then
Range("A1") = Range("A1") + 1

End If
End Sub
As soon as I press save as, the number increases. I want the number to increase AFTER the file is saved. Since it increases when save as is clicked, it stays increased if the user cancels the save. But I think that problem would be fixed if the increment happens AFTER the file is saved.

Questions, comments? Thanks to all for looking and reading!

Hi everyone,

My first post and I am an excel newbie. I have created a client database in Excel 2007, which I have attached. I wish to populate a client invoice in a separate workbook with client address information, invoice item and price based upon the invoice number in Column B. The invoice numbers simply increase incrementally for each database entry. The information that I wish to use to populate the invoice is contiguous in columns C through I, however the locations that I wish to populate the invoice with are not contiguous (other than the client address lines) I simply wish to use the data in the row corresponding with the invoice number to populate the invoice.

I preferred to create the invoice in a different workbook as it is then simpler to convert this to a pdf document format.

I have attached the client database and a sample invoice. I would be grateful for any assistance that anyone could offer.

Many thanks

Dear Excel Forum

I have searched for this and found many threads, but do not know how to apply the answers.

I have a list of invoices that I have entered into an excel document, they are in fields A2:A303, but the list will get longer as I add more invoices.

My invoices are not in numerical order, and there are non-unique entries of the invoice number.

Sometimes, since some invoices are paid earlier than others, I can have an invoice number that is 10-15 numbers ahead of the rest. For example


I'm afraid I may have accidentally forgotten to enter certain invoices, so I need to somehow create a list of invoices that I have missed out in a new column. Admittedly, the list may show numbers 3005-3016, even though I may have not received payments for them yet.

Please advise and I will be forever greatful.

Thank You

No luck finding an answer? You could always try Google.