Auto Running Number in Excel Worksheet

Hi everyone.
Pls review on the attached PO Template, which I use it to issue Purchase Order and keep track on monthly purchases.
This template allow me generate a fresh PO with feature of auto "Naming" which is also the refernce number of the PO.
However, its only allow me to generate at maximum of 1000 POs in same worksheet with limited reference number to 3 digits, i.e. from POFM 001 ~ 1000. If i further generate the PO in this workbook for more that 1000, there incremental sequence number will just not working..

What I wish for improve for this template are as follows:-

1. Is there a way for me to start my running number which is more that 3 digits?
2. As I am creating a new workbook monthly (which is to avoid a heavy file size over time), how I can continue the running number from previous month in another new workbook for coming month?
3. I wish to have same PO refernce number (which is the same as worksheet name) in the body of PO template say in default cell "H4" when a new PO is created, how can I do this with VBA?

Hope someone could help...

Regards, CJ Yap

in the addsheet() macro in module 1 change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and in this bit

Please Login or Register  to view this content.

insert this

Please Login or Register  to view this content.

so it becomes

Please Login or Register  to view this content.

Hi Martin.
Thanks the your help, it work!!
Now I am able to have 4 or more digits for the PO reference no and it also auto insert into the body of PO as well.

However, as I do a test run with starting number other than zero, say with "87500". The next incremental number is just dont create according to this sequence, instead with "875502"?? I have attached my working template in the attachement for your reference. Pls assist me further.

Thanks again..
CJ yap

you are confused by the function of the number format in this line.

Please Login or Register  to view this content.

if should be

Please Login or Register  to view this content.

The zeros in quotes tell the Format function how to represent the value of intLastNum + 1. The zeros are digit holders. The Format help says

Digit placeholder. Display a digit or a zero. If the expression has a digit in the position where the 0 appears in the format string, display it; otherwise, display a zero in that position.
If the number has fewer digits than there are zeros (on either side of the decimal) in the format expression, display leading or trailing zeros. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, round the number to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, display the extra digits without modification.

You may have to revise the code you have for getting the most recent value as that only checks last 3 digits.

Hi Andy. Thanks for your guide.
As I need to start with a most recent number. Say with "87501". How what is the change in the coding?
Pls help..

Rdgs, CJ

How should the program determine start value? Is it read from some cell, or from the sheet tab number or something else?

If you store the number on the template sheet itself how would you determine what the next number should be as it will always be that value +1. That means the number used would be the same the second time the code is run. Should the code also update the template with the next number?

Hi Andy.. On this note, will that be possible if i store the starting number in summary sheet.

The number is taken from B1 on summary sheet.

Please Login or Register  to view this content.

G'day reshm9077 and welcome to the forum,

Please take an extra minute to read the forum rules. Forum Rules link

Especially

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.