Removing one column of data and adding another column

[Rodders]

I want to have a rolling twelve months of data. For example, I will have
Jul08-Jun09(with Jul08 in column E, Aug08 in Column F and so on until Jun09
is in column P) to start and then progressively add months as they occur. So
when Jul09 comes around I want to automatically add Jul09 to the right hand
side of the data and drop Jul08 (so that I have 12 months of data). Aug 08
(and all the data under Aug 08) would then be in column E and Jul09 in column
P.

I want to get this done automatically and not have to mess around with
cutting and adding etc.

Can anybody point me in the right direction and add any hints. I am unsure
where to start.

I am using Excel 2003.

Thanks

 

[Jacob Skaria]

In cell E1 enter a date and format that to display Jul-08..in cell F1 Aug-08
until P1. Now try entering information to column Q. Not in Q1...in any of the
cells below...The below macro will assign the header month in Q1 and shift
the cells..

Right click the sheet tab >View code and paste the below code in the code
panel...Get back to workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("Q:Q")) Is Nothing Then
Range("P1").Copy Range("Q1")
Range("Q1") = DateAdd("m", 1, Range("P1"))
Columns(5).Delete
End If

Application.EnableEvents = True
End Sub

If this post helps click Yes