Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Store Large Numbers in Excel
I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't show numbers that are too ...

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


I would be grateful for any advice or suggestions - I have an invoice and saved it as a invoice template, in office 2007 (I might have to go back to 2003 ).

I have used


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

to increase the invoice number each time that the invoice is opened, which works ok.

The problem that I am having, is that I need to save the invoices individually, but each time I save them and reopen them, the macro requests 'enter a number greater than zero to begin counting with'.

What am I doing wrong?



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 a master template called Invoice master. It has a cell I3 which carries the current invoice number. I would like to do the following:

1) Have an auto_open macro run if the filename is Invoice Master. I have the macro for this and do not need any help with it as it works fine.
2) Have a print macro which runs and then asks to create a new invoice. I have this and it works.
3) When the Yes to the above question is selected, I want it to open the Invoice master and transfer the number from the current invoice to the invoice master and update the number by one. Then run a SaveAs dialogue box to enter in a new name.

When I try to run step three I am having trouble with the fact that the auto open macro in the Invoice Master template fails to run when opened. I don't know if this is because it is being opened from another macro.

I also do not know how to switch between the workbooks as the name is always different. When I copy the invoice number and then open the invoice master, proceed through the SaveAs dialogue box and then try to paste the data into the cell I want it looses the data and the final result is 1 (this is because I have the value of the invoice number as Range("G3") = Range("I3")+1).

1) How do I switch between workbooks when the name is different each time?
2) How do I get the Auto_Open macro in the Invoice Master template to open when I open the workbook from inside a VBA macro?
3) how do I get the data to stay in the queue when switching between workbooks?

Thanks for the help.


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 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.
If anyone has an excel sheet with an example function of this, please email me with an attached example! Thanks in advance for anyones help with this!

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