Incrementing Invoice Number

SNACKOLOGY

New Member
Joined
Jul 29, 2007
Messages
3
Hi ppl,

I am a complete novice so please be patient! :)

I have a workbook, with 226 seperate sheets enclosed. What I would like to do is to put an invoice number in the first sheet's invoice number cell and then the remaining sheets auto add a 1 to make up the next invoice.

For example, if I were to put 1200 into sheet 1's invoice cell, sheet 2's invoice cell would read 1201 and so on. All invoice cells are in the same place and are in B2.

Until I found this board I have had to do this manually! I know nothing about VB but can bake a nice cake if that will help :biggrin:

Thank you in anticipation of your help.

Richard
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
To store all those invoices is very inefficient. you can add say number 1 in the first one then in each of the others use a formula =Sheet1!A1+1, changing the sheet reference each time.
 

SNACKOLOGY

New Member
Joined
Jul 29, 2007
Messages
3
Thanks roy,

Yep, inefficient but works well for me so far. My sheets are called names relating to the stores we service as a company - not Sheet 1 etc. I guess I have to change the ref to the sheet name?
Thanks for your reply
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Hi!

Youa re correct, change Sheet1 to your sheet name.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
I know nothing about VB but can bake a nice cake if that will help :biggrin:
Then you should be able to follow this recipe and you will have implemented some VB code to do what you want. :biggrin:

1. With your workbook open, press Alt+F11 to bring up the Visual Basic window.
2. On the VB menus... Insert|Module
3. Paste the code below into the main right hand pane of the VB window
4. Close the VB window.
5. From the Excel menus... Tools|Macro|Macros...|select InvoiceNumber|Run
6. Add icing if you wish :biggrin:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InvoiceNumber()
    <SPAN style="color:#00007F">Dim</SPAN> InvNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    InvNo = 1200
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        ws.Range("B2").Value = InvNo
        InvNo = InvNo + 1
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Traveler

Board Regular
Joined
May 14, 2007
Messages
149
If you want to put the formula in all 226 sheets (wow really 226) the try this code. As you say you are a novice I will walk you through it.
Press Alt - F11 or right click on one of your sheet tabs and select View Code
From the menu select Insert Module the paste in this code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> insert_formula()
    <SPAN style="color:#00007F">For</SPAN> Sh = 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count
        <SPAN style="color:#00007F">If</SPAN> Sh = Sheets.Count <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        invoiceFormula = "=" & Sheets(Sh).Name & "!B2+1"
        Sheets(Sh + 1).Range("B2").Formula = invoiceFormula
    <SPAN style="color:#00007F">Next</SPAN> Sh
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Now run it.
Hope this helps.
 

SNACKOLOGY

New Member
Joined
Jul 29, 2007
Messages
3
I know nothing about VB but can bake a nice cake if that will help :biggrin:
Then you should be able to follow this recipe and you will have implemented some VB code to do what you want. :biggrin:

1. With your workbook open, press Alt+F11 to bring up the Visual Basic window.
2. On the VB menus... Insert|Module
3. Paste the code below into the main right hand pane of the VB window
4. Close the VB window.
5. From the Excel menus... Tools|Macro|Macros...|select InvoiceNumber|Run
6. Add icing if you wish :biggrin:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InvoiceNumber()
    <SPAN style="color:#00007F">Dim</SPAN> InvNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    InvNo = 1200
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        ws.Range("B2").Value = InvNo
        InvNo = InvNo + 1
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Peter thank you so much. Never used Macros before and this was simply superb to see the invoice numbers auto update. Thank you.

Cream, jam or both? Not sure it will be in date by the time it gets to you. :LOL:

Thanks to Traveler too. Fab site, great ppl.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
Peter thank you so much. Never used Macros before and this was simply superb to see the invoice numbers auto update. Thank you.
No problem. Just keep hanging around this board a bit and you will soon pick up a lot more.

Cream, jam or both? Not sure it will be in date by the time it gets to you. :LOL:
No, I'll have to pass on that one (perhaps just till I come to the UK one day) :p
 

Forum statistics

Threads
1,181,730
Messages
5,931,716
Members
436,800
Latest member
abowalid98

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