Hi, thanks for any help. Im fairly experienced in excel but a novice with VBA.

I have a workbook with multiple sheets each covering a financial year. Each sheet has several tables which have identical layouts and formula except that obviously the years are different. (Twelve columns with a header which is just "Apr-24", "May-24", etc.)

It takes about 5 minutes to manually create a new year sheet by copying the previous year, and updating all the dates. This would need to be repeated for every workbook though so its a big job every year. I thought a VBA automation to iterate the year in a new sheet would be useful, but I'm having weird issues with it. This is the code:

Sub IterateYear()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    Dim oldDate As Date
    Dim newDate As Date
    Dim datePattern As String
    
    Set ws = ActiveSheet
    datePattern = "MMM/YY"
    
    For Each tbl In ws.ListObjects
        For Each cell In tbl.HeaderRowRange
            If IsDate(cell.Value) Then
                oldDate = DateValue(cell.Value)
                newDate = DateSerial(Year(oldDate) + 1, Month(oldDate), Day(oldDate))
                cell.Value = Format(newDate, datePattern)
            End If
        Next cell
    Next tbl
End Sub
This works for some dates some of the time but has really strange inconsistencies. For any year after 2000 it puts it to 2024. However if I manually change the date to 2031 it will then work correctly again up to 2099 before going back to 24. I would assume I've got something wrong in the loop where its still using data from the previous part, but I have no idea why that would only happen in a certain year range. I've tried a similar approach treating the headers as strings: "April-24" etc and if the rightmost 2 characters are an integer, adding one to it, and then updating the string - exactly the same result. Like there is something magic about 2024!

Thanks so much for any pointers.