Incrementing Invoice Number

[SNACKOLOGY]

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

Thank you in anticipation of your help.

Richard

 

[royUK]

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]

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]

Hi!

Youa re correct, change Sheet1 to your sheet name.

 

[Peter_SSs]

I know nothing about VB but can bake a nice cake if that will help

Then you should be able to follow this recipe and you will have implemented some VB code to do what you want.

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

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

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]

I know nothing about VB but can bake a nice cake if that will help

Then you should be able to follow this recipe and you will have implemented some VB code to do what you want.

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

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

Thanks to Traveler too. Fab site, great ppl.

 

[Peter_SSs]

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.

No, I'll have to pass on that one (perhaps just till I come to the UK one day)