VBA to Iterate Dates in Table Headers

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:

Please Login or Register  to view this content.

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.