Excel Number Auto Increment Help!

Excel Number Auto Increment Help! - Excel

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.

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

Similar Excel Video Tutorials

Increment Word & Number 2 Methods.
- See how to do create a column of words and numbers quickly with two different formulas and a mouse trick for Paste Special Values. See how to incremen ...

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

VB:

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()
Clear
End Sub

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

Hi
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

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'

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

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.

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?

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
ActiveWorkbook.Save
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 rjsept@shaw.ca 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.

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.

Jane

Code:

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

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.

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 ?

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.

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
was there...lol

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.