Serial number with last 4 digits resetting every year.

[Nesha]

Hi
I currently have a great macro that, when a "generate" button is pressed, it puts out a serial number in the format of YYMMXXXX (where XXXX begins from 0001) and populates the next available row with this number. However we now need it to reset the last four digits to go back to 0001 every time a new year has begun.
The year and month are automatically input from "today's" date.
Having a little trouble trying to tweak this so that the numbers reset :/
Any help will be greatly appreciated ☺

 

[Andrew Poulsom]

Welcome to MrExcel.

Please post your macro.

 

[Nesha]

Hi, Sorry...posted the question from my phone last night. Here's the macro:


Sheets("Do Not Modify").Select
SerialNo = ThisWorkbook.Worksheets(2).Cells(1, 1)
ThisWorkbook.Worksheets(2).Cells(1, 1) = SerialNo+ 1
Range("B13").Select
Selection.Copy
Sheets("Serial Number Generator").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F7").Select
ThisWorkbook.Save
Range("I4").Copy
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll


Thank-you

 

[Nesha]

253
	5/02/2015 =TODAY()
15 =YEAR(B2)-2000	02 =TEXT(B2,"mm")
	253 =A1
SERIAL:	S-15020253 =CONCATENATE("S","-",A4,B4,(IF(B5<10,"000",IF(B5<100,"00",IF(B5<1000,"0",)))),B5)

<tbody>
</tbody>

(Couldn't paste the screenshot - but for demonstration purposes the Serial No is supposed to be output into B13 - not the 6th row down)
A1 is the cell that is referred to in the beginning of the macro- its the number that changes and that I want to go back to 1 when a new year starts.

Hopefully it makes a little more sense now...let me know if any more information is needed

 

[Andrew Poulsom]

Maybe try:

    SerialNo = ThisWorkbook.Worksheets(2).Cells(1, 1)
    If Mid(Sheets("Serial Number Generator").Range("I4").Value, 3, 2) = Format(Date, "yy") Then
        ThisWorkbook.Worksheets(2).Cells(1, 1) = SerialNo + 1
    Else
        ThisWorkbook.Worksheets(2).Cells(1, 1) = 1
    End If