Posts by hsdacct

    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.

    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.