Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Excel Number Auto Increment Help!

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, I'm totally new to VBA and no nothing about it other than how to copy and paste code in. I've been trying to design an invoice where the number increases. I have so far managed to get code working from my template that increases the number every time the template is opened however if i open an invoice i have already done it still increases the number on that invoice.

The code i'm using is: Code:

Private Sub Workbook_Open()
    Range("H6").Value = Range("H6").Value + 1
End Sub

So basically i want the number to increase each time on the template but not on saved invoices. I know there are many topics about this already but i really have no idea how to adapt code to make it work for my worksheet. So could any help please be explained to me step by step.

thanks in advance

View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Increment a Date By X Days for Each Row in Excel
How to quickly increment a date by a certain number of days for each entry. Type the first date in a cell. Make sur ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Get the Row or Column Number of a Cell in Excel
How to get the row or column number of the current cell or any other cell in Excel. This tutorial covers important ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Similar Topics


I am looking to save an excel invoice with the following characteristics:

1) It gets saved in a specific folder with certain cells as the file name
2) Specific contents get cleared
3) Next time I open the Workbook, the Invoice Number increases by one

I currently have this coding:

Sub SaveInvoice()
Dim NewFN As Variant
' Copy Invoice to a new workbook
NewFN = "/Users/Invoices" & Range("F7").Value & "_" & Range("F1") & "_" & Range("B7") & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=x1OpenXMLWorkbook
End Sub

A new workbook is created but it doesn't save. Is there an easier way to code this so that once I click the button the Invoice automatically saves, the contents are cleared and the invoice number increases by 1?


Sorry, I didn't quite know how to word the title .

I have an Excel Invoice Template, saved as a template.

I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)

After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.

What I need to know is:
Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?

I have gotten so much information from this site Im so glad it is here and hopefully I can help someone out in the future


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.


Private Sub Workbook_open() 
     '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 me in support of injured Royal Marines


I am not very good at Exel and need some help please!!!!!

I have designed an Invoice template and need to increase the invoice number by one each time i open the Invoice template. The number is in cell D8. I wish to start my Invoice Numbers from 40. The worksheet in which I need to insert the code is called 'Invoice'

Can some please help me with the code.


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!

Good afternoon,

Any help would be great. I have a good understanding of Excel however I am very new to VBA Macros.

I have created an invoice template within excel. I have Macro's within this invoice to:
- provide next invoice number upon saving current invoice
- save completed invoice to specified file
- clear template so ready to use again for next invoice number.

What I now require is code which enables me to copy certain cells upon saving each invoice and compile them into an excel table (within the same workbook but different sheet) each time the save button is clicked (once invoice has been completed)

Info I would like to copy to the table upon each completed saved invoice:
"E5 Invoice Number"
"A11 Client"
"E4 Date"
"C10:E13 Project Details"
"E46 Balance Due"

I have attached the document I am trying to make these changes to.

Many thanks in advance


I have a very simple macro that I found on the internet that increases an invoice number by 1.

Sub NextInvoice()

Range("L1").Value = Range("L1").Value + 1

End Sub

It works great, provided the invoice number is just numbers. But I want to be able to add PF as in PF101425, on some of the invoices, not every invoice, however when I put the PF there the macro does not work.

There is more to the macro than just increasing the invoice number. It also populates other cells on a separate sheet with the information from the invoice and then clears all fields ready for the next invoice. So when I click on the button that triggers the macro the fields are populated correctly, even with the PF in front of the number, it is just the invoice number does not increase by 1.

If anybody can help with this problem it would be appreciated. Thank you.

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?

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 am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.

For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.

I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")

Does someone mind having a look in to the attachment and letting me know what I have done incorrectly and how I can fix it?

I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.

Thank you

I have an "invoice" template that I have created. Each time I open the template I would like Excel to auto generate a sequential invoice number. For example, when I open the template I would like it to assign invoice number "A-431". I will fill out the invoice and save it as a worksheet. The next time I open the template it should assign invoice number "A-432". Is this possible?


I am looking for some suggestions on how can I possibly withdraw main data from my Invoice template (like: Invoice number, Name of the customer, total amount) in to another spreadsheet in order for me to create some sort of data base of every invoice that I send to my customers. I also wonder if some how I can auto increase my invoice number? For example if you open new invoice with number 001 so that the second one that you open will be automatically changed on 002 and so on, but with question If you reopen an existing invoice the number should not increase then.

I know that VBA can help but since I do not know anything about VBA I am looking for some help here.

Thanks in advance

I would like to know if it is possible to auto number invoices. We use an invoice template from Excel but we need to make sure that any that are printed and sent are accounted for. Right now anyone can print an invoice. We would like the invoice to automatically be assigned a number when the template is opened.

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.

Hi All,
Was referred to this board from Microsoft Office Newsgroup. Perhaps somebody has some experience with this.
I'd like to have users open an Excel template and have an invoice number inserted automatically into a field (K8). The number must be unique (i was thinking of using the date/time down to the second) and be saved permanently so that everytime the same document is opened and/or altered the invoice number remains the same. When the template is used again for a new document, a new invoice number must again be generated for the new .xls document.
Anybody have any ideas, any Excel guru's out there done anything like this before?

Thanks. Shaun

I have been working on this for a few days now, trying to get this done by myself but just cant seem to find the right way.

I have created a list that has invoice numbers that will automatically be used based on the information given by a template that I have created. I cant use the same Invoice number again so each time this template asks for an invoice number a new one needs to be given. The template is basically a workbook that has all of the information that I need on a day to day basis. The list has the invoice numbers that I need but i also want to know to what file this invoice number is going to. I want the template to insert information into the list to track the invoice number. Can this be done and if so is there a direction someone could push me in to get me on the right track? I have gone thru the excel bible twice with no luck. Thanks!

I am a newbie at all this, however I managed to figure many things out by simple trial and error. However I am totally stuck on this.

I have a set inventory on my first worksheet called "Inventory Sheet". I then created another worksheet for template invoicing on the next tab so that as I go from client to client and make a sale I can simply copy and make a new invoice from the original template invoice progressively. The thing is, I would like to have a formula that I can put on the template invoices that will continue to deduct from the perpetual inventory on the Inventory Sheet worksheet. I want to keep all invoice as tabs on the bottom.

For example:
Initial Phone Inventory = 25
1st invoice I sell 5 phones ("Invoice A")
2nd invoice I sell 2 phones ("Invoice B")
3rd invoice I sell 6 phones ("Invoice C")

I want the "Inventory Sheet" to say I have a total of 12 phones left in stock.

I am wondering if there is a way to progressively post data to the "Inventory Sheet" and automatically deduct from the inventory as I create new invoice worksheets.

Sorry if it's too confusing! I would greatly appreciate anyone's help!

Hi all!

I'm very new to using macro's and i don't really know what I'm doing.
I've got myself a bit confused with this one. I am trying to write a macro(or a couple?) which when used, opens another workbook then copy the invoice details to the second workbook, save the invoice as a seperate file, then clear the invoice and increment the invoice number, and closes the second workbook.
I think I have macros to open the second workbook, increment the invoice number and save each invoice as seperate file, however I can't seem to get it to copy data to second workbook and move down a line. in preparation for next invoice.

The first file is "Invoice template" and the details are on the sheet called "Invoice" in cells F4,F5,F6,G18,G41
The second file is called "Bookkeeping" and the sheet the need to go to is "Invoice List" starting in cells A3,B3,C3,D3,E3 respectively for the first invoice then moving down a row.

For some reason I just can't get this to work at all! I have tried recording a macro but had no joy. I must admit that the macro's I used for the other actions were not written by myself, but shamelessly google'd on the internet.
Ideally I would like to make it so that it all happens in one big macro but I don't know if this is possible?

Thank you all in advance


I've stolen this code from here... Code:

Private Sub Workbook_Open()
Dim response As VbMsgBoxResult
response = MsgBox("Update reference number?", vbYesNo + vbQuestion)
If response = vbYes Then
    With Sheets("Sheet1").Range("A1")
        .Value = .Value + 1
    End With
End If
End Sub

this is the often asked invoice increment type question... but i'd like to add to it..

if the user saves the workbook as a different name.. i.e. the workbook changes from being the template to being a fixed record of the order/invoice that was raised how can this "Update reference" Msg box be stopped ? .

I need a robust system that will stop the possibiltiy of the audit trail being lost.

I'm thinking auto save as a different name ? but i'm not sure what the other options are ?

ALSO as an challenge the original template will need to be a shared workbook - a number of people in the team use it - how on earth would this work ?

So i want a number of people to be able to use the shared workbook, and it increment by one, but without the possibility of two having the same number. Would an open event work in this case... or does the shared status prevent this ?

Help appreciated, as always. Chuf

How could i change this vba code


Private Sub Worksheet_Change(ByVal Target As Range)

  Dim shSource As Worksheet, shDestination As Worksheet, iR As Long, i As Byte
  Set shSource = ActiveSheet
  Set shDestination = Sheets("Invoice Template")  ' =Sheet4
  If (Not Intersect(Target, shSource.Range("J5:J500")) Is Nothing) And Target.Cells.Count = 1 Then
     If UCase(Target.Value) = "Y" Then
        Dim aso(), aco()
        aso = Array("A", "C", "D", "G", "F", "H", "I")
        aco = Array("B14", "F16", "D35", "D36", "D37", "D38", "D39")
        iR = Target.Row
        For i = 0 To UBound(aso)
        Range("J" & (iR)).Select
    ActiveCell.FormulaR1C1 = "DONE"
            shDestination.Range(aco(i)).Value = shSource.Range(aso(i) & iR).Value

    End If
  End If

End Sub

To add this vba before the final shDestination.Select


    Sheets("Invoice Template").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Sheets("Invoice Template").Select
    Selection.PasteSpecial Paste:=xlPasteValues

I have added it but it does not seem to work
My main aim is so when a "Y" causes this vba to run, before shDestination.Select is executed i would like to run the code above.

Hope you understand this

Many Thanks

Hey guys,

I run a small printing company and can't afford to purchase expensive accounts/order management software so I'm making my own in Excel, all went well until I hit a problem and I'm hoping someone can help (and Excel is capable of such a function): to ensure accuracy I need my invoice template to accept multiple line invoices.

Instead of trying to explain my setup I've attached what I've done so far with dummy information for a single 2 line invoice.

As you can see my "invoice" page works nicely simply by entering the invoice number (dummy order 110001). But what I need Excel to do (ideally) is to fill out the invoice lines referring to the "Invoices" page. This would be very easy to do for a single line via VLOOKUP, but I can't work out how to operate this for multiple lines, I don't want each 'product' being listed as a separate invoice number because that makes an accounting nightmare.

My best guess would be a very long string of VLOOKUPs and IF functions to reference the multiple identical invoice numbers, and only fill out the next line of the invoice if there is a duplicate invoice entry on "Invoices" that hasn't already been entered onto the invoice template .

If any of that makes sense I'd really appreciate some help, I can see me copy and pasting the wrong product into the invoice template at some point and causing a problem.

Thanks for any advice,


I need help... i tried pasteing the code below but nothing happens. can you
help me out. i am trying to set up auto save as at close of workbook. file
name should equal contents of merged cell I10:J10 on sheet2. this save as is
important because it is the customer number which i need for searching as
customer list grows.

also i need to place an auto date that is not volitile in merged cell I1:J1
this is needed to generate the customer number in the first place... right
now i am using Ctrl + ; to place the date in the cell. this is ok if i must
but with expected increase in customers i could save a lot of time if it just

i am running xp with excel (office pro) '03.

"Frank Kabel" wrote:

> Hi
> in a macro
> sub save_it()
> dim fname
> with activeworkbook
> fname = .worksheets("sheet1").range("A1").value & ".xls"
> .saveas fname
> end with
> end sub
> "-tinka" wrote:
> > Is it possible to make an Excel (2003) template (or a macro in the template)
> > to save a file with a specific cell-contents as file name? For example an
> > invoice-template where a cell contains the invoice number. I want it to
> > automatically save the file as "invoice-number".xls in default file location.
> >
> > Thanks... :-)

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

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.

Hi all,
I am a novice wrt Excel. I had created a simple Invoice, to save as a
template. Would like to have the Invoice Number cell automatically in
running number, for each invoice I created. Example my first invoice will
have the number 001. Once finished and saved, opening up another new
invoice, using the template, it would now show 002, and so on.

Truly appreciate some advise to do this.

Thanks in advance.