Macro to copy down only the last 4 digits of the cell above then add 1

[sspatriots]

I have a column that is set up as a pattern that includes the current year, a dash, and the next sequential 4 digit number. When I run the code to insert my new row at the bottom of the table, I want this code to look at the cell directly above in column A and just bring down the last 4 digits +1. Out in front each time, I need the current 4 digit year, a dash and finally that sequential part of the entry above.

If the last entry on the list is 2022-0234, I need the current year to come down for the next entry and the next sequential number of the last 4 digits like this when I select the add new row to table button:

2022-0235


If I'm on the last day of the year (December 31st) when the last entry was added and it looked like this:

2022-0843, then I want it to start the sequence over again on January 1st with 2023-0001.


I have no idea where to begin with all of this. All I have is a simple code that adds the next blank row.

Sub AddNewJobToJobList()

Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert

End Sub

 

[jasonb75]

Do you have anything in other columns that extends below the last used row in column A? If you don't then there is no need to insert a new row, you only need to do that if you have existing data that needs to be moved down first.

Something like this should do what you're asking for

Sub sspatriots()
Dim rng As Range
With Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Set rng = Range("A:A").Find(Format(Date, "yyyy") & "-", , xlValues, xlPart, xlByRows, xlPrevious, False, False, False)
    If Not rng Is Nothing Then
        .Value = Format(Replace(rng, "-", "") + 1, "0000-0000")
    Else
        .Value = Format(Date, "yyyy") & "-0001"
    End If
End With
End Sub

 

[sspatriots]

Wow, that is amazing! I would have thought this would have been a lot longer code from everything I was seeing when searching for a solution first. Would there be any way to make the focus go directly to the last number added when the code is run so the user could start there and start populating that row across? To answer the question, there is no other information in the new row when we add the next one that would have to be moved down.

 

[Peter_SSs]

This may also do what you want? (Assumes at least one such entry already in the column)

Sub NextSeq()
  With Range("A" & Rows.Count).End(xlUp)
    .Offset(1).Value = IIf(Val(.Value) = Year(Date), Format(Replace(.Value, "-", "") + 1, "0000-0000"), Format(Date, "yyyy-0001"))
    .Offset(1).Select
  End With
End Sub