I am trying to record a macro that will take the value of a cell and autofill
the next cell to the right. The problem I have is that the cell references
will be variable.

To try to explain this better, I have a spreadsheet with columns for each
month of the year that show values from another worksheet. At then end of
each month, I want to create a new column to insert that month's data. No
problems so far.

Once the column is inserted, I am trying to put a heading at the top of the
column (e.g. July 2005) by autofilling from the previous column (e.g. June
2005). But I cannot work out how to write my code so it will also work for
August, September etc.

Any help with this would be most appreciated.

Thank you
MMH

Try using the date function in the cell. Something similar to:

Range(YourRange).FormulaR1C1 =
"=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,DAY(RC[-1]))"

For a formula similar to:

=DATE(YEAR(G1),MONTH(G1)+1,DAY(G1))

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"MMH" wrote in message
...
I am trying to record a macro that will take the value of a cell and
autofill
the next cell to the right. The problem I have is that the cell
references
will be variable.

To try to explain this better, I have a spreadsheet with columns for each
month of the year that show values from another worksheet. At then end of
each month, I want to create a new column to insert that month's data. No
problems so far.

Once the column is inserted, I am trying to put a heading at the top of
the
column (e.g. July 2005) by autofilling from the previous column (e.g. June
2005). But I cannot work out how to write my code so it will also work
for
August, September etc.

Any help with this would be most appreciated.

Thank you
MMH

Thank you - that does exactly what I want it to.

MMH

"Damon Longworth" wrote:

Try using the date function in the cell. Something similar to:

Range(YourRange).FormulaR1C1 =
"=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,DAY(RC[-1]))"

For a formula similar to:

=DATE(YEAR(G1),MONTH(G1)+1,DAY(G1))