Hiding Named Sheet

[menor59]

Hello all,

I was wondering if the following was possible...

I have a work Book that for the year every week its filled out. IN theory 52 Work sheets

They are named in the following Format...

MMM DD, yyyy

I was wondering if a Code could be written to Hide all Sheets Excluding BLANK But keep This weeks Current Visible and 5 sheets Backwards...

So i would end up with tabs that say

BLANK
Feb 17, 2013
Feb 24, 2013
Mar 03, 2013
Mar 10, 2013
Mar 17, 2013

March 17 is this weeks current sheet.

Next week I would have when the workbook is opened

BLANK
Feb 24, 2013
Mar 03, 2013
Mar 10, 2013
Mar 17, 2013
Mar 24, 2013

etc...etc...

Id like this to run when the Work Book is open..

Thoughts??

[Teeroy]

The following code should do what you want (in a standard code module). You can call Show_Last_5_Sheets from the Workbook_Open event to run it automatically.

Sub Show_Last_5_Sheets()
Dim i As Integer
Dim DateArray() As Long
Dim LastDateNo As Integer
Dim sht As Worksheet
ReDim DateArray(0 To Sheets.Count - 2)
i = 0
'make array of sheet names (as dates) and hide all sheets but BLANK
For Each sht In Sheets
If sht.Name <> "BLANK" Then
DateArray(i) = DateValue(sht.Name)
sht.Visible = False
i = i + 1
End If
Next
'Sort Arry of Dates
BubbleSort DateArray
'Find closest date to Now; today or earlier
LastDateNo = Application.Match(CLng(DateValue(Now())), DateArray, 1) - 1
'unhide last 5 sheets
For i = WorksheetFunction.Max(0, LastDateNo - 4) To LastDateNo
Sheets(Format(CDate(DateArray(i)), "MMM d, yyyy")).Visible = True
Next i
End Sub


Public Sub BubbleSort(ByRef lngArray() As Long)
' Credit: Squirm
' http://www.xtremevbtalk.com/showthread.php?t=78889
Dim iOuter As Long
Dim iInner As Long
Dim iLBound As Long
Dim iUBound As Long
Dim iTemp As Long, i

iLBound = LBound(lngArray)
iUBound = UBound(lngArray)

'Which bubbling pass
For iOuter = iLBound To iUBound - 1
'Which comparison
For iInner = iLBound To iUBound - iOuter - 1

'Compare this item to the next item
If lngArray(iInner) > lngArray(iInner + 1) Then
'Swap
iTemp = lngArray(iInner)
lngArray(iInner) = lngArray(iInner + 1)
lngArray(iInner + 1) = iTemp
End If

Next iInner
Next iOuter

' For i = 1 To UBound(lngArray)
' Debug.Print lngArray(i);
'Next i
End Sub

[SamT]

:rotflmao:

[Teeroy]

OK SamT granted, very inefficient but at least it worked. I shouldn't code with insomnia :banghead:. The Bubblesort segment I had was the first thing to come to mind. After a bit of clearheaded thought (and a lot of coffee) the following makes more sense.

Sub Show_Last_5_Sheets2()
Dim i As Integer
Dim DateArray() As Long
Dim sht As Worksheet

i = 1
For Each sht In Sheets
If sht.Name <> "BLANK" Then
If DateValue(sht.Name) <= DateValue(Now) Then
'make array of sheet names (as dates) earlier than now
ReDim Preserve DateArray(1 To i)
DateArray(i) = DateValue(sht.Name)
i = i + 1
End If
'hide all sheets but BLANK
sht.Visible = False
End If
Next
'unhide sheets that are 5 (maximum) largest dates earlier than now.
For i = 1 To WorksheetFunction.Min(5, UBound(DateArray) - LBound(DateArray) + 1)
Sheets(Format(CDate(WorksheetFunction.Large(DateArray, i)), "MMM d, yyyy")).Visible = True
Next i
End Sub