Group worksheets together under 1 tab?

[jmcgurl]

Hi,

I am not sure if these is possible but I currently have 1 workbook with 7 worksheets (1 for each day of the week). In the past, I would start a new workbook each week for that weeks data. However, I have now been tasked with creating 1 large workbook for each month.

With that said, instead of having 31-37 tabs on the bottom is there a way to group the worksheets together so that when Week 1 is selected it opens into just its 7 worksheets?

Thanks for your help!

 

[StephenCrump]

You could show the relevant 7 worksheets, and hide the rest. Something like this perhaps, assuming you have range names for "WeekNo", and "StartDate", and that your sheet names can be converted to dates, e.g. "2-Mar-15", "3-Mar-15" etc.

If each week starts on Monday, say, and StartDate is Monday 30 March 2015, this code will hide any date sheets other than "30-Mar-15", "31-Mar-15", ... "5-Apr-15".

'In the sheet module containing range name "WeekNo" 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim dteStartDate As Date
    Dim lVisible As Long
    
    If Intersect(Target, Range("WeekNo")) Is Nothing Then Exit Sub
        
    dteStartDate = Range("StartDate").Value
    
    For Each ws In Worksheets
        lVisible = xlSheetVisible
        On Error Resume Next
        lVisible = CVDate(ws.Name) >= dteStartDate And CVDate(ws.Name) < dteStartDate + 7
        On Error GoTo 0
        ws.Visible = lVisible
    Next ws

End Sub

 

[jmcgurl]

Thank you for your response and help. After a lot of thought I ended up creating a totally different workbook.