Unique number not in order

Hi, I got this excel file. I wanted to add in new data but recently, I discovered that the unique number (New/2017/xx) is not in order. For example, my last entry unique number is New/2017/49 then when I add in new entry, the unique number will be New/2017/32. Hope someone enlighten me on this. I have attached my file for reference. Thank You

PS: My original file contained 2000 plus data and it contain my work information. I have replaced it with other data and all the vba macro are the same. Currently, I add in new data, the unique number is in order.

In UserForm1 code. You will find below line.

Please Login or Register  to view this content.

Basically, Counts item in AllNews sheet that meets "News/Year/*" and add that number + 1 as News ID.

Since ExpiredNews can move from AllNews sheet to ExpiredNews. It will not be unique ID.

Ex. You had News/2017/1 to News/2017/10 in AllNews. Then moved 1 to 5 to ExpiredNews. Then next item created will have News/2017/6 as ID (which will be duplicate ID).

As long as you don't move items that are in same year to ExpiredNews. You will have Unique ID generated in order.

Is there any way which I can moved the expired news but still able to genreate the unique ID

Actually... come to think of it. It's far easier to count both sheets for same condition and add 1 in this instance.

No need to introduce additional complexity.

Changed following section of UserForm module.

Please Login or Register  to view this content.

See attached.

Hi, thanks, it works. Just to check, if let's say a brand new year start already, will this still works?

Yes it will. CountIf will return 0 and code adds 1. So new year will start with News/Year/1 always.

Oh ok. Thanks a lot