How to Autofill a column in VBA for current month and Autofill next column for month after that?-CodePudding

I am trying to Autofill the next column with the previous column formulas, everytime I run the macro. So it should be offset by 1. I am currently refreshing my data every month and am trying to autofill the column for each new month with a simple macro in excel.

For Example when I run this Macro:

Sub check()
' Keyboard Shortcut: Ctrl e
'
    
        Range("CH2:CH236").Select
        Selection.AutoFill Destination:=Range("CH2:CI236"), Type:=xlFillDefault
   
End Sub

It takes this data of November month with formulas, and autofills it for next column(about 234 rows) that is december.

Column:CH            Column: CI        Column: CJ 
|Nov-21|              |Dec-21|
|  6023|      ===>    |  7021|
|    90|              |    82|
|    37|              |    67|
....                  ....

So what I would like to do is when the new month comes (January) to run the macro so that the next column(CJ) after Dec-21 gets autofilled with Jan-21 data and so on without manually changing the range.

So it should be something like:

If Row "CI2" = "" THEN Autofill that column's rows
ELSE Autofill Next Column's rows if previous column is already autofilled ...

What code should I add so that it automatically picks the next column without manually doing it. Any guidance would be appreciated.

CodePudding user response：

Think something like this should work.

Sub check()

Dim c As Long

c = Cells(2, Columns.Count).End(xlToLeft).Column 'last used column in row 2

Range(Cells(2, c), Cells(236, c)).AutoFill Destination:=Range(Cells(2, c), Cells(236, c   1)), Type:=xlFillDefault
   
End Sub