Solved Macro for Generate next Reference Number

March 14, 2013 at 06:28:03
Specs: Windows 7
I wish to create a purchase order form and automate a macro button to generate the PO number in a specific format of DPT-YYMM-XXX.

DPT will be a 3-letter department code assigned for each different department. YY will be the last 2 digit of the year it was created. MM will be the month it was created, but I need it to be in 2 digit format. XXX will be a running number that will be in 3 digit format. The running number will only be reset to 001 at the start of the year.

The number will be depended on the PO date the user entered in a cell e.g. C2. So if I create my first PO on 1-Apr-2013, the reference number will be DPT-1304-001. Then maybe the next one on 20-May-2013 the reference number will become DPT-1305-002.

The problem comes when I want to automate this process but still keep the format for the numbers.
I wish to create a macro assigned to a button which will generate the next PO number when the users press the button.

I prepared an extra worksheet to do the workings.
Where cell C2 will reference to the cell in the Purchase Order worksheet where they input the date.
And I created the following formulas :
In cell C5 =C7&"-"&C8&C9&"-"&C10
In cell C8 =RIGHT(YEAR(C2),2)
In cell C9 =LOOKUP(MONTH(C2),C14:D25]
In cell C10 I wish to have the reference number which I tried to show it in 3 digit format so I put as '001

See More: Macro for Generate next Reference Number

Report •

March 15, 2013 at 06:32:56
I have some questions:

1 - Where is the department code e.g. DPT coming from? Are you referencing a cell to pull in the department code?

2 - Let's say you have 2 departments, ABC and DEF. Let's say they both create a PO in the same month. Would the last 3 characters still increment by 1 even though the departments are different?

Are you looking for this:


or this:


2 - What do you have in C14:D25? In other words, what is it that you are using the LOOKUP function for?

3 - Are you storing the the PO numbers some place? I'm wondering if you can reference the last PO created and simply add 1 to the last 3 characters.

For example, suppose the last PO created stored in E4. Can you reference that cell and use something like the following formula?

=(Build Department and Date string here)&<b>TEXT(RIGHT(E4,3)+1,"000")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 23, 2013 at 20:49:17
✔ Best Answer
Hi, thanks for the help.

I thought I attached a print screen of how my workings worksheet looks like, but apparently it failed to attach to this article.

In answer:
1) I had set it to refer to the Department Code in C7 where I will key in the 3 letter alphabet since we plan to give each dept one file each.
2) The last 3 chars just need to keep increase by 1 actually since each dept will have their own file. The number will be reset after a year.
3) C14:D25 is a table referencing to different period of the financial year since we don't follow calendar month, the first period 01 starts from Apr actually. So I used a lookup function.

But my problem was solved.
Recently I managed to find an article to add one line to my vba code to increment the value by 1 each time it runs.
Range("D10").Value = Range("D10").Value + 1

And another article that solved the problem to keep the reference number in 3 digits.
In cell C10 I input this formula

Might not be the best way, but it solves my problem using the simplest functions.

In the end my [Workings] worksheet looks something like this:

C2 1-Apr-2013   
C6 =C7&"-"&C8&C9&"-"&C10
C10 =TEXT(D10,"000")
D10 1

Report •

Related Solutions

Ask Question