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 Video Tutorials

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

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 have a copy button on my invoice that copies the invoice in and moves it to the front of my workbook,increases invoice number by 1 and clears contents in specific cells.This works fine but sometimes I would like to be able to copy an old invoice that might be in the middle of the workbook and move to the front increasing the invoice number by 1 more than the highest invoice number in the workbook,(would be the first invoice in workbook).At times I will have 50 or more invoices in this workbook.I hope I have explained this good enough.I will attach the macro I am using right now.Thanks


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

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

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!

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 am not an experienced exel user, but I am pretty computer literate in general, here is my situation, i have an invoice template that i got and modified slightly (compaany logo and a couple of other adjustments. What I am trying to do is get the invoice number to increase by one every time I open the file, I thought that this would be an easy task, but after extensive bowsing I am still not there, I trie a couple of different things, viewing the code, and pasting this
Private Sub Workbook_Open()
Range("A1") = Range("A1") + 1
End Sub
but changing a1 to the cell I want the numbet to change in, this does not work for me, I think one of the posts said it was because I am using an excel template file to start with??????? I am not here by means of lack of effort, I would really like to figure this out, I would also like to add some drop down menus, but that seems way out of my league at this point in time, could someone PLEASE help me figur out this numbering invoices thing, I am loosing sleep over this
I can be emailed at if anyoen can help me I would really appreciate that

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!

Cell D15 is my Invoice Number Field.

I have entered my invoice number starting from 001. I am doing this manually every time after referring the previous Invoice Number in the next sheet( i.e., Sheet 2)

I have a macro "SAVE" in Invoice Page, which copies the S.No, Name , Qty and Amount from the Invoice page to the next Sheet i.e., ( Sheet 2). This copies row by row in the next page after every Invoice is done.
After Copying the above said datas, I have another Macro "NEW" which deletes the S.No Field, Name, Qty and Amount Field for keeping the Invoice ready for the next Feed.

Is it possible to auto increase the invoice number + 1 in the Invoice Filed, based on the copied Invoice number in the Next Sheet ( Sheet 2 ). Provided, hitting the "New" button for the next feed should not affect the S.No Field.

Kindly Help.



Sub DeleteStuff()
    Dim c As Range
    For Each c In Range("D15,C18:C20")
        If Not IsNumeric(c) Then c.ClearContents
        If IsNumeric(c) Then c.ClearContents
    Next c
End Sub


Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text
For x = 1 To r
If .Cells(x, 2).Text = InvN Then
MsgBox ("Receipt " + InvN + " has already been Saved")
GoTo endd
End If
Next x
For a = 2 To 8
Select Case a
Case 2
.Cells(r, a) = Cells(15, 4)
Case 3
.Cells(r, a) = Cells(18, 3)
Case 4
.Cells(r, a) = Cells(20, 3)
Case 5
.Cells(r, a) = Cells(22, 3)
Case 6
.Cells(r, a) = Cells(24, 3)
Case 7
.Cells(r, a) = Cells(26, 3)
Case 8
.Cells(r, a) = Cells(13, 4)
End Select
Next a
End With
MsgBox ("       Data Saved")
End Sub

Good morning!

I got my invoice and my journal. Once I finish my invoices I send all to the journal, but I am having a little problem.... Here is my code


  Dim Ws As Worksheet
  Dim Jrn As Worksheet

  Set Ws = Sheets("Sheet1")
  Set Jrn = Sheets("Sheet2")

    Jrn.Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

    Jrn.Range("A65536").End(xlUp).Offset(1, 0).Value = Ws.Range("E2")

End Sub

Sometimes with the invoice not all the rows that I am copying have items. Therefore, when it is pasted into the journal, I may only have 2 lines, but the invoice number ends up only in the first blank row. I have to change it manually.
Is there a way that I could do it automatically. In other words, everytime I paste however many lines, I also want to paste the same amount of invoice numbers.

Thanks for the help!


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

I use a simple template in Excel for invoicing. I make an invoice and when I
save the file, my clients name and the total of the invoice are then saved
to another Excel file that tracks all my invoices for the year. I upgraded
to Excel 03 and now it does not transfer the value in my invoices to the
tracking sheet. When I open my invoice template I get a " Cannot open
template wizard" error. It;'s been so long I don't remember how I set this
system up in the first place.

Can anyone help me fix this so it will work in 03?



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

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,