Excel open page - dynamically show latest 6 months

[sbalajis]

Below is my data in sheet "Sheet1"

IDJan-13Feb-13Mar-13Apr-13Apr-13May-13Jun-13Jul-13Aug-13Sep-13
A112162646102050402515
A224182426202020202525
A348153018301010102045
A416512010405020303015

I wish the data should be visible only for latest six months. for example, if the current month for Oct-13,

IDApr-13May-13Jun-13Jul-13Aug-13Sep-13
A1102050402515
A2202020202525
A3301010102045
A4405020303015

and if the current month is Nov-13, the values for Oct-13 will be visible.

IDMay-13Jun-13Jul-13Aug-13Sep-13Oct-13
A1205040251510
A2202020252525
A3101010204535
A4502030301530

remaining columns should be hidden. If there is no data in Oct-13 the current month view should be same as Sep-13. I have month row is filled till 2014 and each row has formula based on another sheet "Sheet2". If I add data for oct-13 in "Sheet2", the data will be visible for Oct-13 column in "Sheet1" otherwise it will be blank. It will be helpful if someone help me how to make this dynamic using excel VBA.

Thanks in advance.


Assuming my header is in row 2 and i am picking up the current date by today() function. I am checking the current date with the month mentioned in the same column. today() function is to be moved to next cell when there are values in that column. otherwise it remains in the same column. Here is my workaround for the above task. i want this needs to be simplified and working.

Sub Workbook_Open()
Cells(A1).Select
Selection.End(xlToRight).Select
r = Selection.Row
c = Selection.Column
If Month(Cells(r, c)) = Month(Cells(r + 1, c)) + 1 Then
ActiveCell.Offset(0, -7).Select
Range(Selection, Cells(ActiveCell.Row, 2)).Select
Selection.Hide
Cells(r, c).Select
ActiveCell.Offset(1, 1).Select
Range(Selection, Cells(ActiveCell.Row, Selection.End(xlToRight).Select)).Select
Selection.Hide
Else
Cells(r, c).Content.Cut
ActiveCell.Offset(0, 1).Select
Selection.Paste
End If
End Sub

 

[Debraj]

Hi S Balaji,

Welcome to the forum..

Try this..

Sub HideSheet()
Dim deb As Range
Set deb = Cells(1).CurrentRegion
Columns.Hidden = False
    For i = 1 To deb.Columns.Count
        If IsDate(deb.Cells(1, i)) Then
            If Abs(DateDiff("m", Date, deb.Cells(1, i))) > 6 Then _
                deb.Cells(1, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

I have used this code in Sheet Activate Trigger..

You can use it in any Button or Selection Change Event.

Let me know if you are searching for only previous month.. not the up-comming month also.

PS: If some one is watching this thread with a radar.. just to inform him.. DateDiff works find in VBA..

PPS: as you are a new member.. please spend some time to read the forum rules..
I have moved your code to VBA section.. rather than Dashboard section..
Please choose appropriate section for your query..

 

[sbalajis]

Hi Debraj,
Thanks for the code. This code is giving some idea on how to approach the problem but it is not solved it fully.

I feel I have not asked my question very clearly. If the current month is Sep, I will have the data till Aug and the excel should show the data from March to August. Remaining months should be hidden(for the upcoming month also). Only if the data available for September month, the sheet columns will be visible from April to Sep otherwise it will be March to Aug (Even if the current date is in October). Hope this clarifies.

Thanks for moving the question to right section. I will take care of this from my next posts.
Regards,
Balaji

 

[Debraj]

OK..
Assuming..
* Your data are Numeric.
* You want to view last 6 month from the first (from back side) filled any cell of that month
* and if 4th lst or 5th last column have no data.. still show it..\
* and you have only data upto this month..

hope this will work.. in workbook_open call below code..

Sub HideSheetCond()
For i = Sheets(1).UsedRange.Columns.Count To 2 Step -1
    If IsDate(Cells(1, i)) And WorksheetFunction.Count(Columns(i)) > 1 Then
        Range(Cells(1, 2), Cells(1, Cells(1, i).Offset(, -6).Column)).EntireColumn.Hidden = True
        Exit For
    End If
Next i
End Sub

if not.. please upload a sample file with color which need to hide..

 

[sbalajis]

Hi Debraj,
I have uploaded the file which contains 2 sheets. Both have the same data. For illustration purpose I have put it in separate sheets. I have coloured the columns which need to be hidden.
Thanks and Best Regards,
Balaji

 

[Debraj]

try the attached..

 

[sbalajis]

Hi Debraj,

Thanks for the workouts. This code is almost solve my problem.

My concern is I will feed the data for Sep13 by second week of October. If I use the CurrentRegion, it will show 6 months data but the last column (which is Sep13 in our case) would be blank until I feed the data for it. The view should be Mar13 to Aug13, even the file open in Oct, till I feed data for Sep13. Is there any way to crack this.

Thank you once again for helping out.

Regards,

Balaji