Free Microsoft Excel 2013 Quick Reference

Auto Incrementing an Invoice Number

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.


	VB:
	
 Workbook_Open() 
    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,
Bromley


Post your answer or comment

comments powered by Disqus
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 ?

Eric@Shreveporthandyman.com

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

I am trying to creat an invoice template using Excel 2002. My purpose is to
auto increment the invoice number whenever I make a new invoice.

Hello again!

I need to create an invoice number based on the name of the product I am inputting. The name of the invoice must be in the following format:

[1st four letters of the product name] + EXE + Date (format mddyy)

To get the name of the product, the end user is asked via InputBox. This result is defined as Entry1. So, if a product is called BANANAS, the invoice needs to be called:

BANAEXE53105

My dilemma is, I can't get Excel to display the first four letters of Entry1. I already have it figured how to display as EXE53105. THAT particular line of code being used is:


	VB:
	
[B17] = "EXE" & Format(d, "mmddyyyy") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
d was previously defined as the date.

If anyone could offer any suggestion, it would be most appreciated.

Thank you, as always!

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,
-pp-

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,
-pp-

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,

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

Maggie

Not sure how to title this thread.
I have a userform that feeds a spreadsheet named Labor. tbId_num is the textbox that popluates the next empty cell on the sheet starting at C3. The format of the text is 025-11. The numbers increment from there 026-11, 027-11 and so on.
Is there a way to create a Userform_Initialize event that would would read the last number in the column, auto increment the first number only by one and populate the textbox on the userform with that number when the form initializes? I would like this to be an intialize event becuause multiple entries are sometimes added, and a workbook_open would force them to close. I am open to other suggestions. Please let me know if you need anymore information.

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

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

3001
3002
3016
3004

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

I am trying to create a new purchase order system, whereby, I fill in all relevant information, ie, the supplier name, the items ordered, etc. But I would like the purchase order number to auto increment upon opening a NEW form, not when opening ones I've already created. Also, I wouldn't mind if I could have the date automatically come up on new forms. I have tried several VB threads in my document for the po number auto incrementing, but I always get error messages and when I go to open a new one to see if it's worked, I get asked if I want to debug, when I say yes, it doesn't do anything. If anyone could please help me out. I am new to VB and macros, but I am very computer literate. However, if you can spell out any suggestions as much as possible, I would appreciate it. Thanks in advance to the help.

I am trying to get my invoice sheet to automatically increment the invoice number when I open the workbook. I know it is in the code i.e. private sub? but it doesnt seem to work. Any help?

Thanks,

Kim

Hello All,

I have a set of sheets where I put in various types of call centre data - one line item per call. The sheet where this line is entered depends on the type of call.

I would like to have a unique number assigned to the line item when that line is filled (regardless of the sheet on which I happen to be). I think that the easiest thing would be to have a sheet containing the base number from which that unique item number is calculated. For example, I begin answering a call. I enter the date on the next empty line in the sheet according to the call type. Tabbing over to the next cell (column C) will pull the reference number from the base number sheet and put it in the first cell of the line I'm entering. The base number is incremented and overwritten on the base number sheet. I continue to fill in the line. For the next call, I may go to the next empty line on that sheet or it may be another sheet. However, entering a date in column B would still cause the reference number to be automatically entered into that line's column A with the number on the reference number sheet to be auto-incremented.

So, if anyone understands what I'm trying to say, how could I go about doing this?

Cheers,
Notwen

invoice.xlsHi all, this has no doubt been asked many times...

I had an Excel invoice up and running with auto date and invoice numbers. I accidentally saved over it so that the date and invoice number became static.

I have been unable to get it to work again, been trying for ages! since Friday 13th when I messed it up!

I have cut and pasted from:http://www.mcgimpsey.com/excel/udfs/sequentialnums.html where I originally got the code.

Is there any way I can upload the file for help??

Many many thanks in advance.

P.s was getting error 9 subscript out of range, got it opening without error messages, but not counting up, then tried different codes from different places, now I'm totally lost.
Chris.

There used to be an invoicing toolbar in Excel 2000 that allowed you to
assign the invoice number and save the data in a database

I need some assistance with sequential numbers.

I currently have a spreadsheet with an invoice template i have created. In one cell i have an invoice number which i want to be able to generate a sequential invoice number by the click of a button.

There is also another workbook that will store the generated invoice number and go down the list and so on.

What i would like to do is:

1. When i open the template, i would like to click on the button to generate the next sequential invoice number.
2. Once this number is generated, i would like it to auto populate in the other work book which store the invoice numbers.

Any guidance or help would be awesome.

Thanking you gurus in advance,

Regards,

Dan.

Hi all, this has no doubt been asked many times...

I had an Excel invoice up and running with auto date and invoice numbers. I accidentally saved over it so that the date and invoice number became static.

I have been unable to get it to work again, been trying for ages! since Friday 13th when I messed it up!

I have cut and pasted from:http://www.mcgimpsey.com/excel/udfs/sequentialnums.html where I originally got the code. but can't get it to work now.

I would need the invoice numbers to start 1001, otherwise I presume I'd have to open and close the file loads of times to avoid duplicating previously used invoice numbers from when the thing was working?

Any help would be much appreciated!

Many many thanks in advance.

P.s was getting error 9 subscript out of range, got it opening without error messages, but not counting up, then tried different codes from different places, now I'm totally lost.

Edit: I have now put attached the correct file.
Chris.

Hi I am trying to sequentially increase an invoice number when opening the file. (to prevent having to rely on typing in a fresh number each time). I am fairly ok with using Excel but not VBA, willing to try anything though so please can you tell me where i need to go and what i need to do!

I will need to save the invoice template as 'save as...'

many thanks

Looking for so help:
I have a macro that creates an invoice number, increased by one on each printing event, in a cell in the active worksheet, and then a macro places the invoice number in the left header of the invoice. The problem is that the 2 macros that Im using work fine until I try to reset the invoice number's range back to zero. For whatever reason, when I reset the invoice value back to 0 + 1, the next printing sends about 9 pages to the printer with the invoice number only on the first 8 pages, and the final page is printed with the invoice and header. Can someone point me in the right direction in finding a solution?

Sub NumberBefore_Print()
Range("AA1").Value = Range("AA1").Value + 1
End Sub

Sub PrintWS()
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
LookIn:=xlValues, SearchDirection:=xlNext).Row
Range(Range("A1"), Range("B" & lastrow)).Select
With ActiveSheet.UsedRange
Sheet1.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address
Sheet1.PageSetup.LeftHeader = "Reciept Number " & Range("AA1").Value
.PrintOut Copies:=1
End With

End Sub
Thanx...

I am trying to create a simple Point Of Sale system in which, after checking (check-box) and marking the quantities (spinner) of products/services, it fills in the Invoice automatically with the Description, Quantity, and pricing on a separate sheet. I have everything setup, I just can't figure out how to make the invoice fill out on its own with the selected products/services.

Well, I looked at the Vlookup and can't seem to make it do what I want. Of course I am not an expert at Vlookup either. There are two sheets. Worksheet One has all the item descriptions, pricing, and quantities. Worksheet Two is an invoice. What I am trying to do is this:

As I select the item, via a checkbox, and enter the quantity amounts, I want it to appear automatically on the invoice with the description pricing and quantity.

How would I go about getting the invoice to do what I want?

How can I set up automatic sequential invoice numbering across multiple
worksheets but in the same workbood? I have all my customers set up on
individual worksheets and would like to assign an invoice number when I
print. Then would like the next time I print possibly a different worksheet
to print the next number up from the previous number that was assigned to the
last invoice.

Excel 2003...Is it possible to create an invoice that can be linked to a
worksheet whereby selecting a record in the worksheet then activating a
custom command (macro maybe) the invoice will be populated with the data from
the record? I'm thinking along the lines of a Word mail merge type function
but the invoice must have formulas in it.


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