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

**henderg** · 01-25-2010, 12:57 PM

Hi everyone,

I'm new to forums, so I apologise at the start for any protocol mistakes I unwittingly make.

I'm also new to VBA Programming, so be gentle with me :o)

I'm writing some code to insert a column into a table in Excel 2003. The table will end up being 13 columns wide at the end of 2010, i.e. the column headers will eventually run from Dec-09 to Dec-10. I need some code to populate the new column header with a date e.g. Jan-10, using the previous column header as the starting point Dec-09 in this case.

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.

**Richard Buttrey** · 01-25-2010, 01:18 PM

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

and drag it across the next 10 columns.

HTH

**Palmetto** · 01-25-2010, 01:20 PM

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

**henderg** · 01-26-2010, 05:47 AM

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.

**henderg** · 01-26-2010, 06:07 AM

Thanks Palmetto. I copied your code to mine without success, so I'll try to attach a wee spreadsheet that shows an extract of what I'm working on, including my macro for inserting a new column.

I tried using the function =EOMONTH(A1,1) to enter the date in the next column, and I got the #NAME? response.

**henderg** · 01-26-2010, 07:22 AM

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?

**Palmetto** · 01-26-2010, 09:05 AM

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.

Please Login or Register  to view this content.

**henderg** · 01-27-2010, 05:44 AM

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

**Palmetto** · 01-27-2010, 08:21 AM

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.