Unique Sequential Numbers

You are too modest, Papo. When you deal with Excel you should allow your imagination to roam more freely. For example, why ask to insert a number while in reality you want a row with the number already in it? Or, why do you need a button while looking at a worksheet consisting, literally, of a billion buttons?

The code I offer you below allows you to double-click on the cell immediately below the last available number, in fact, the blank cell where you want the new number to appear. It will insert a row coping all cell formats and formulas from the row above and insert the new number (changing count with the year as you describe).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 166
 
    Const NumberClm     As Long = 1             ' change to suit (1 = column "A")
    Dim Trigger         As Range
    Dim Rnew            As Long                 ' new row number
    Dim Number          As Long
    
    Set Trigger = Cells(Rows.Count, NumberClm).End(xlUp).Offset(1)
    With Target
        If .Address = Trigger.Address Then
            Number = .Offset(-1).Value
            Rnew = AppendRow(.Row)
            Cancel = True
            
            With Cells(Rnew, NumberClm)
                .Value = NextNumber(Number)
                .NumberFormat = "00-000"
                .Offset(0, 1).Select                ' select the next cell
            End With
        End If
    End With
End Sub
 
Private Function AppendRow(ByVal Rt As Long, _
                           Optional ByVal Rs As Long) As Long
    ' 166
    ' return the number of the inserted row
    
    ' copy from previous row (can't create first data row) if Rs isn't specified
    If Rs = 0 Then Rs = Rt - 1
    With ActiveSheet
        .Rows(Rs).Copy
        .Rows(Rt).Insert Shift:=xlDown
        On Error Resume Next
        ' delete any existing values
        .Rows(Rt).SpecialCells(xlCellTypeConstants).ClearContents
        On Error GoTo 0
    End With
    
    AppendRow = Rt
    Application.CutCopyMode = False
End Function
 
Private Function NextNumber(ByVal Number As Long) As Long
    ' 166
 
    Dim Annual          As Integer
    Dim Serial          As Integer
 
    Annual = Int(Number / 1000)
    If Annual = (Year(Date) Mod 1000) Then
        Serial = Number Mod 1000
    Else
        Annual = (Year(Date) Mod 1000)
    End If
    NextNumber = Annual * 1000 + Serial + 1
End Function

Important! Install the code in the code module of the worksheet on which you want the action.

The code is structured in 3 easy to maintain parts. The first part replaces the button. It's an event procedure which fires when you doujble-click and ensures that action is taken only when one particular cell is clicked. You can help define the cell by changing the value of the constant NumberClm.

The event procedure then calls the other two functions. The first one inserts a new row and informs the caller of the number of that row. The second one calculates the new number from the old.

Finally, the event procedure does two more things.

1. It formats the cell where the number appears. This is because there is a number in that cell, like 21001, not a  text string like "21-001". That makes it easier to work with the number because, well, because it's a real number. However, you want the number to appear like 21-001. Therefore you need to apply a cell format. Of course, as you copy rows down the format is also copied. Inserting it isn't therefore necessary, strictly speaking, but I added it just to be sure
2. The procedure then selects the next cell because, of course, you will not start writing in the cell where the number was inserted but somewhere else. You can change the code to select the cell you want to start working at.

Papo

This will work if your tracking numbers are stored sequentially in column A of the sheet where your button is (like my file attached) AND the last entry is the last number allocated. Click the green button (currently in column A) and new numbers will be added.

It works by finding the last used row. The If test (in bold below) compares the last two digits of the current year (from Year(Now()), i.e. the year of todays date ), say 21, with the first two digits of the last used cell in A. If it's the first row (so no numbers yet), it makes n become 1.  Likewise if early next rear (or later years) it compares "21" from the last cell with 22 for the new year.

Otherwise, it adds 1 to n (the last 3 digits of the last tracking number).

Then it sets the next cell down to the two year digits, plus a "-" and the incremented number.

Private Sub CommandButton1_Click()

Dim r As Integer, n As Integer

r = Range("A" & Rows.Count).End(xlUp).Row 'find last used row

If Right(Year(Date), 2) <> Left(Range("A" & r), 2) Then 'if no tracking number yet or year changed
    n = 1
    Else 'normally take last 3 digits of last and add 1
    n = Right(Range("A" & r).Value, 3) + 1
End If
'set next cell to desired format
Range("A" & r + 1) = Right(Year(Now()), 2) & "-" & Format(n, "00#")

End Sub

You can't check that the year end bit works as it stands but you could do this check (to simulate a change of year) to see that the trailing number bit resets: 1) Click the button a few times so you have numbers in column A. 2) Change the If statement above to this (in the VB Explorer):

If Right(Year("01/01/2022"), 2) <> Left(Range("A" & r), 2) Then

(which will give the year of that bold 22 date) and if you click the button again, the numbers will restart from 001. 3) Undo that change in VB Explorer and delete the test values in A.

Hope this works for you.

Range("A" & r + 1).Select

 Worksheets("Input"). Range("B7") = Right(Year(Now()), 2) & "-" & Format(n, "00#")