VBA Code to automatically fill dates horizontally (not to last column)

[Vega144]

Hello everyone! This is my very first post on mrexcel.com but I've been a site regular for many months now. You guys rock! I always find a thread of someone having the same doubt as myself, but I now have a somewhat specific query: I have a data sheet that is updated monthly where monthly accidents are loaded, as well as the accumulated accidents of the current and past year and the goal.

	A-AO​	AP​	AQ​	AR​	AS​	AT​	AU​
1​	...	sep-21	oct-21	nov-21	2021 Accumulated	2020 Accumulated	Goal
2​	...	Value	Value	Value	Sum Value	Sum Value (from other sheet)	Value

This is then graphed on a time series where the last three columns (the accumulateds and goal) are represented as bar charts on the same graph. Because of this reason, everytime a new month is added I must "sandwich" it between the previous month and the 2021 Accumulated. To do this I always insert a new column between those two and then drag the fill handle of the month to automatically add the current month and then manually add the value of the accidents below.

I believe there must be a way to automate this with VBA. I've seen other people with similar issues but they often only want to autofill the date to the very last column (or row if they are doing it vertically). But the column I want to autofill is not the last one, so I'm not sure how to approach this. If you wise fellas could help me with a macro that could create a space between the last added month and the 2021 accumulated columns and then autofill it with the next month to be added I would be extremely thankful! THANKS!

 

[rollis13]

Would be very helpful to know how month/year in row 1 is generated so, for now, I'll guess and assume that you have a formula like this: =DATE(YEAR(AQ1),MONTH(AQ1)+1,1) in cell AR1.
I also assume you have only 3 used columns after column AR.
This could be a quick solution to paste in a standard module:

Option Explicit
Sub Insert_Month()
    Dim lc     As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column - 2               'find last column -2
    Cells(1, lc).EntireColumn.Insert Shift:=xlToRight                   'insert column
    Cells(1, lc).FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,1)"  'insert formula for new month/year
End Sub

 

[Vega144]

Would be very helpful to know how month/year in row 1 is generated so, for now, I'll guess and assume that you have a formula like this: =DATE(YEAR(AQ1),MONTH(AQ1)+1,1) in cell AR1.
I also assume you have only 3 used columns after column AR.
This could be a quick solution to paste in a standard module:

Option Explicit
Sub Insert_Month()
    Dim lc     As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column - 2               'find last column -2
    Cells(1, lc).EntireColumn.Insert Shift:=xlToRight                   'insert column
    Cells(1, lc).FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,1)"  'insert formula for new month/year
End Sub

Hello, rollis! Thanks for replying! I actually don't have a formula to generate the date. I just wrote "jan 2021" and excel converted it into a date (01/01/2021). I then changed the format to be mmm yy and lastly dragged the fill handle so it automatically changes the following cell to the next date. But I tried your code and it does just what I needed!
Thank you so very much!

 

[rollis13]

Glad it works. Now let's make it dinamic, let's suppose next year you add a 2022 Accumulated column, this other macro detects the last date (month/year) even if you add even more columns.

Option Explicit
Sub Insert_Month()
    Dim lc     As Long
    Dim x      As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column   'find last used column
    For x = lc To 2 Step -1                             'step back through cells row 1 from last
        If IsDate(Cells(1, x)) Then Exit For            'if date is found exit for
    Next x
    lc = x + 1                                          'assign new last column number
    Cells(1, lc).EntireColumn.Insert Shift:=xlToRight   'insert new column
    Cells(1, lc).FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,1)" 'insert formula for new month
End Sub

 

[Vega144]

Glad it works. Now let's make it dinamic, let's suppose next year you add a 2022 Accumulated column, this other macro detects the last date (month/year) even if you add even more columns.

Option Explicit
Sub Insert_Month()
    Dim lc     As Long
    Dim x      As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column   'find last used column
    For x = lc To 2 Step -1                             'step back through cells row 1 from last
        If IsDate(Cells(1, x)) Then Exit For            'if date is found exit for
    Next x
    lc = x + 1                                          'assign new last column number
    Cells(1, lc).EntireColumn.Insert Shift:=xlToRight   'insert new column
    Cells(1, lc).FormulaR1C1 = "=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,1)" 'insert formula for new month
End Sub

You're amazing! Thank you lots! Have a happy new year!

 

[rollis13]

Hope it's a good year for all of us?.