Unique order number

  • Hey all,


    I've been fighting with this VBA code for awhile now.. and not making much headway. I'm going to explain my use case first and then I'll explain the little progress I've made lol..


    What I need is a unique order number based on the date. Ideally, it would use a YYMMDD format, and then increment if there were multiple orders in the same day, though I would get by pretty happily with a date value as well (43900 or w/e).

    I'm using a master worksheet that then is filled out, and saved via a VBA macro button which takes the customer name and this order number, then saves it as a new file with that information as the document name. My worksheet has an input sheet, a bunch of form sheets, and then a new sheet I want to use to track the order number.


    Ok. My goal is that when a macro button in the worksheet is clicked, a new number based on the date is generated in the first blank A cell in that 9th sheet, preferably protected, so it can't be manually changed by someone who shouldn't be in there, then in B1, it would count the number of the same dates if there's duplicates, then in C1, concat those numbers together to end up with something like "2003133" if it's today's date and the 3rd time the button was clicked today, thereby having 3 instances of 3/13/20 in the A column. Then I can use that C1 reference in the main sheet to create the order number. I'd guess at some point after the order number is made, the master worksheet has to be overwritten to retain the new instance of the button being clicked and order number created. This is my dream! There are many like it, but this one is mine! I have another button that will save the master file as a new file based on the customer name and order number.


    A bit of a wall of text, but I was trying to be as thorough as I can. I've gotten it to where it's selecting the new blank cell in the 9th sheet, but I can't figure out how to do anything useful with that number, save it, and then increment based on how many entries there are.. I haven't figured out how to get it to output a NOW() formatted as YYMMDD yet either..


    Any advice would be greatly appreciated! I'm continuing to fiddle here.

  • Insert following excell formula in (example) cell A1 and insert order start number example (100) in cell P1

    ="Order"&TEXT(TODAY(),"yymmdd")&P1


    Insert following vba code in sheet module, change cell address as per your location (example P1)


    You can change word "Oder" to anything else you want.


    The code will generate order number using current date and pretext text at the beginning "Order" and number end the end with auto increment, your order worksheet must be save to get auto incremental number at the end.




    Code
    1. Private Sub workbook_open()
    2. Range("P1").Value = Range("P1").Value + 1
    3. End Sub
  • Thanks for that! Is there a way to have it check and reset the counter if the new date doesn't equal the old one? Part of this effort was to shorten the order number. The way it is in your example above, it will slowly grow in length over time.

  • Sorry I am new to coding not able to figure out how to counter check.

    If you want to keep order number length sorter you can remove "Order" text and order start serial number to 1, also you can reduce date format say yymm follow with serial number. it will be (20031) 2nd order will be (20032) it's less possibilities to get duplicate.

    Currently this method I am using for my quotation and invoicing.

  • Hi HS


    See if the attached can be modified to suit:


    ordernumber.xlsm


    Cheers

    Justin

  • Jack in the UK

    Changed the title of the thread from “Stuck! Unique order number” to “Unique order number”.