Hiding Columns, based on date

[dg74]

Hi,

Can anyone help me, i'm sure what i need to do is possible, but not sure how.

Basically I have a spreasdheet which has 2 worksheets

one worksheet contains each week end date with a week no

the other sheet contains all 52 weeks of the year in columns, on this sheet is also a week end date.
I want to do a lookup to determine which week of the year it is, then automatically hide the all but 16 of the 52 columns.

Ie if the week end date is apr 8th (week 1), hide columns 17-52, therefore only show columns 1-16 (4 months at a time)

If anyone could help, I would really appreciate it.

 

[dg74]

anyone?

 

[jmthompson]

Let me re-state to be sure I understand-

Sheet 1 contains a list of week-ending dates in one column and the next column contains the week #.

• Do these week #'s match the excel week #'s?
• What cell contains this week#?

Sheet 2 contains a cell with a week-ending date

• Is this validated against the list in sheet 1?

Sheet 2 also has 52-53 columns with week numbers

• which row are these week numbers in?

You want a macro to check the week-ending date in your cell on sheet 2, lookup the assigned week # and then hide all columns except for the 15 to the left of the found week number- showing 16 weeks in all.

• What should happen if the week-ending date is less than 16 weeks into the year, so there are less than 15 columns to the left of your week#?

 

[dg74]

Hi,

Thanks for the reply.

Yeah sheet 1 contains week numbers in column A (from 1 to 52) and in column B is week ending dates, which is always a friday in my case. so week 1 has a week ending date of friday 8th april

Sheet 2 contains a cell with the current week ending date automatically inserted. then there are 52 columns with the week number on one row and the week ending date on another column.

I want to take the current week ending date, then automatically hide columns based on what week number it is, basically I only want to to show the current week + 16 (ie 4 months at a time). when I get to the last 16 weeks of the year I will just want to show them each week so when I reach december, I will always show Dec 2011, Jan 2012, Feb 2012, Mar 2012 (16 weeks)

I hope this is clear.

The need to do this is the spreadsheet is too big, data gets entered into it each friday, but only for the current week, so I dont need to show the whole 52 weeks at a time.

Thanks

 

[jmthompson]

The year wrapping complicates matters. At what point will you change sheet1 to start including the following year? And will you recycle week numbers?

 

[dg74]

once I get to the end of March 2012, all data will be wiped, I will then just input the new week ending dates for 2012-13 and start again.

 

[jmthompson]

ohhhh, week 1 starts in April 2011. That makes things easier.

 

[dg74]

yeah, sorry I forgot to mention, I am using the uk financial year, April - March

 

[jmthompson]

Try this code, in a copy of your file.

The code assumes your curent week-ending date is in "Sheet2", cell "A1", so you may need to update the MyDate line.

It also assumes that your week-ending date/week number lookup values are in "Sheet1" Range "A1:B52". So MyWeek may also need to be updated

Finally, it assumes that your week columns are on "Sheet2" from "B1:BA1". You may need to update these references too.

Sub DisplayMine()
Dim MyWeek As Long
Dim MyDate As Long
Dim c As Range

MyDate = Sheets("Sheet2").Range("A1").Value
MyWeek = Application.VLookup(MyDate, Sheets("Sheet1").Range("A1:B52"), 2, False)
Range("B1:BA1").EntireColumn.Hidden = True

For Each c In Range("B1:BA1")
    If c = MyWeek Then
    Range(c, c.Offset(, 16)).EntireColumn.Hidden = False
    End If
Next c
    
End Sub

 

[dg74]

thank you so much for this, really appreciate it. I will try it tomorrow and get back to you.

Thanks again