Macro to create dynamic headers

[ANE0709]

since discovering this site you guys have been super helpful so i came back for more.

im building a worksheet with VBA and have the foundation all laid out. my current code takes data that is exported from an external source, cleans it up, adds formulas, and does a whole lot of calculations. My problem is specifically the headers... Currently i created the headers from scratch and do not have a working vba to help create them going forward. I need some sort of code that will dynamically generate the headers for me if possible. Any help is appreciated.

- Only Columns M1-AN1 need to be generated using VBA
- this is a 25 month report
- I need to concatenate the year and the month. the year is constant but the month shifts left as the oldest month falls off column M and the new month is added to column AM

Raw Data for January

Raw Data for February

What the final should look like. Always M1-AN1

 

[BSALV]

Sub Months_25()
     Range("A1").Name = "StartDate"                             'named range, this cell contains a date in the 1st month
     arr = [transpose(substitute(text(Eomonth(startdate,row(a1:a25)-1),"'mmmm-yyyy"),"-",char(10)))]     '25 months, replace the "-" by a vblf
     Range("M1").Resize(, UBound(arr)).Value = arr
End Sub

 

[ANE0709]

Sub Months_25()
     Range("A1").Name = "StartDate"                             'named range, this cell contains a date in the 1st month
     arr = [transpose(substitute(text(Eomonth(startdate,row(a1:a25)-1),"'mmmm-yyyy"),"-",char(10)))]     '25 months, replace the "-" by a vblf
     Range("M1").Resize(, UBound(arr)).Value = arr
End Sub

thank you. I tweaked it a tiny bit and it worked.