Automatic invoice numbering

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
Please I need help with a vba code for automatic invoice numbering.

We have an Excel invoice template used for generating invoices. In the past, the invoice numbering has been done manually and this has led to the problem of missing out some number ranges and also having more than one invoice with the same number.

We want to ensure that when future invoices are generated, they are numbered automatically and sequentially. Currently, for each invoice, the invoice number is typed into Cell B12, and the current number is S1204. We want the automatic and sequential numbering to start from S1205, with the number inserted into the appropriate cell i.e. Cell B12.

A vba code that will help us achieve this will be greatly appreciated.

Thank you.

Kenny
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Hi, maybe something like this...

Code:
Sub Inv_No()
Dim Inv As Long
With Range("B12")
    Inv = Right(.Value, Len(.Value) - 1) + 1
    .Value = Left(.Value, 1) & Inv
End With
    
End Sub
 

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
Thank you Neil for your kind response.

I have a few questions and don't know if you or any of our other experts on the forum could help.

1. Does it matter that we want the automatic and sequential invoice numbering to start at S1205?

2.We would not want to create invoice numbers that will never be used. Therefore, what happens if someone opens the invoice template, enters a few details and then CHANGES his/her mind and closes the template without going ahead to generate the invoice?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
3.Is it possible for the code to achieve the following:
<o:p></o:p>
(a) save a copy of the invoice with the Invoice Number as the name of the file and then clear the entry data to get ready for the next invoice; and
<o:p></o:p>
(b) When the workbook is closed, the invoice is cleared and saved as the Master Invoice.
<o:p></o:p>
Thank you.

Kenny
 
Last edited:

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
Please can the Experts on the forum help with my Post #3.

Thanks in anticipation.

Kenny
 

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
Hi All,

I refer to my Posts #1 and #3 respectively, and still ask for help from the Experts on the forum.

I have searched around and looked at some of the solutions suggested on the subject. However, none of the suggested solutions yet takes into consideration some important issues that might arise in automatic invoice numbering as follows: There are issues such as 'What if the file is opened and closed again without producing an invoice? & 'What if the invoice is printed twice?' I believe that these are some very important considerations in designing macros for automatic invoice numbering.

I believe that in order to overcome some of those problems, the routine of entering invoices should be to open the Master Invoice; Enter and save as many invoices as needed; Close the workbook and save as Master Invoice, over-riding the original Master Invoice. This will save the latest invoice sequential number.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
This means that there should be THREE buttons on the Invoice Sheet as follows:<o:p></o:p>
<o:p></o:p>
1. New Customer button - used to assign Invoice Number<o:p></o:p>
2. Save Invoice button - saves a copy of the invoice with the Invoice Number as the name of the file and then CLEARS the entry data to get ready for the next invoice.<o:p></o:p>
3. Close Workbook button - clears the Invoice and saves the workbook as the Master Invoice.<o:p></o:p>
<o:p></o:p>
Please, I am therefore seeking your help with vba codes for the three buttons that I need to place on the Master Invoice - with the ideas stated above - to help us to generate invoices with automatic & sequential numbering.

Thanks for your anticipated help.
<o:p></o:p>
Kenny<o:p></o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top