Generating incremental invoice number based on date

Hi,

Currently I am using the below code to generate new invoice number like A2B-1001 where A2B-1000 is already put in cell F2. When i trigger this macro it clears certain cells and changes the value in F2 to A2B-1001.

This is done with the following line in the attached code:
Range("F2").Value = Left(Range("F2").Value, 4) & 1 + Mid(Range("F2").Value, 5, 4)

Please Login or Register  to view this content.

But I need the invoice number to be like YYMMDDNNN where NNN will change like 001, 002, 003 etc invoices made on same date.

This forum is of great help & I hope that I will not be disappointed.

Thanks in advance.

Originally Posted by Leith Ross

Hello excellon,

Is the invoice number affected by any other code or formulas in the workbook?

Hi Leith,

No, it's independent of any other code or formula.

Originally Posted by Leith Ross

Hello excellon,

The easiest way to do this to use two cells. In the macro I chose F2 and F3. Changes these what ever you want them to be.

In cell F2 is the formula "=Today()" and if F3 is a 1, the starting serial for the day. The macro formats these as YYYYMMDDNNN in the email. After the email is sent, the serial number will either be increased by 1 or reset to 1, if the date has changed.

Please Login or Register  to view this content.

Hi Leith,

Sorry for my late response. Actually I did not receive the notification.

I already had a today's date formula in cell F9 and F2 is the cell where I put the serial number 01. And I have made a slight change in your code & it works.

Please Login or Register  to view this content.

Thanks a lot for your time and effort.