Rolling 12 Profit and Loss Statement

[KSU1296]

I currently have a profit & loss statement in excel by our calendar year. I have one workbook for our 2009 financials and one workbook for our 2008 data. Columns and rows in both are the same and the columns are the months, running from January to December, and the rows are the data with each column summed and then a final column showing the total of all twelve months. Like this:


JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total
Sales
COGS
ETC...
Total

What I've been asked to do is to setup a rolling p&l as well as our original P&L in a separate worksheet. So, right now in our 2009 financials there would be a separate tab with March 2008 through Feb 2009 showing. Do I have any options instead of just adding the most recently completed month at the end and deleting off the oldest month?

I can combine the two workbooks into one to get all the data together if I need to, but I'm looking for some way to make it dynamic such that Excel knows based on the date to only visibly show the last twelve months from the date. So, if I open the workbook today (March 5th) it will show the last twelve months, but if I open it April 2nd, it will show April 08-March 09.

 

[MickG]

Hi, If you combine the Years into one sheet and your Headers are dates i.e :-Jan-08 Feb-08 Mar-08 Apr-08,---Jan-09--, then the following code will hide the Columns a Year old or older.
head

Sub Hide()
Dim Rng As Range, Ac As Range
Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
For Each Ac In Rng
  If Month(Ac) < Month(Now) And Year(Ac) < Year(Now) Then
        Ac.Columns.Hidden = True
    End If
Next Ac
End Sub

Regards Mick

 

[KSU1296]

I'm not a VBA guy though. How do I put actually put the code into my file?