Autofilling a date from one cell to the next cell on its right

Hi,

Why not just create your 12 monthly columns up front and hide future columns if necessary.
Then the task becomes one of unhiding columns as necessary, probably using a current month column variable with something like

Range("A1").Offset(0,<columnvariable>).EntireColumn.Hidden = False

To quickly create 12 dates Put 31/12/2009 in the first column and then in then next column just enter

=EOMONTH(A1,1)

and drag it across the next 10 columns.

HTH

Thanks Richard, but I'm creating this spreadsheet for Excel novices to use, and I also want it to look as clean as possible: I don't like having hidden cells.

By the way, I tried using the =EOMONTH(A1,1) function, and I got the #NAME? response.

the column headers will eventually run from Dec-09 to Dec-10 . . .

I can't use cell references as these will change as the table grows, so I'd appreciate an alternative that will work as the table is updated each month.

Based on your description there is no reason, that I can see, why a formula would not work. You seem to be moving across the columns to the right, adding a new month. If this is correct, then this approach will work,

For more precise help, upload a sample workbook and provide more clarity about the insertion of columns.

Option Explicit

Sub Insert_Column_Date()

Dim lcol As Long

lcol = Cells(1, Columns.Count).End(xlToLeft).Column

Cells(1, lcol + 1).FormulaR1C1 = "=EOMONTH(RC[-1],1)"
Cells(1, lcol + 1).NumberFormat = "mmm-yy"

End Sub

Richard and Palmetto: I've got the =EOMONTH() function to work now, after adding the analysis toolpack, but I still can't get Palmetto's code to work.

Palmetto - Could you explain what each part of your code does to better my understanding please?

Code revised per your sample worbook with comments explaining. See attached.

I also added an optional line of code to convert the date formula into a static date. You can comment out or delete this bit of code if you want to retain the formula.

Option Explicit

Sub Insert_Column_Date()
    
    Dim lcol As Long
    
    'determine last used column using row4 (header row)
    lcol = Cells(4, Columns.Count).End(xlToLeft).Column
    
    'insert a new column beginning with last column
    Cells(4, lcol).EntireColumn.Insert
    
    'insert formula into header of new column and apply date format
    Cells(4, lcol).FormulaR1C1 = "=EOMONTH(RC[-1],1)"
    Cells(4, lcol).NumberFormat = "mmm-yy"
    
    'optional code to convert formula to static value
    Cells(4, lcol).Value = Cells(4, lcol).Value

End Sub

Thanks very much Palmetto. Your assistance is greatly appreciated. I especially like the comments explaining what each part of the code does.

Glad to help. If this solution meets your need then please be sure to mark the thread as solved by going to the top of YOUR first post and clicking on Thread Tools, then Go Advanced and choosing the solved prefix.

Leaving feedback / adding to the reputation of those who contributed a helpful response is appreciated. See my signature for how to go about it.