VBA - Hide Rows (Named Range) ALL worksheets, based on one cell reference

[JSpaced]

Hi guys,
I've been doing some googling and searching on this forum and I can't quite find the answer I need.
So here's the situation:
I have a spreadsheet of students in tutor groups (about 15 students).
First worksheet is the summary sheet and gathers together data based on the data in the individual student worksheets.
Each student worksheet has a daily record: a grid of scores each day over an 8 week period.

I have named ranges on each student worksheet (week1, week2, etc.)

Up until now I've used the following code, operated by a click button on each student sheet to hide everything except the current week for data entry:

Sub ShowWeek1()
If InStr(1, ActiveSheet.Range("J2").Value, "1") > 0 Then
    Range("Week1").EntireRow.Hidden = False
Else
    Range("Week1").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "2") > 0 Then
    Range("Week2").EntireRow.Hidden = False
Else
    Range("Week2").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "3") > 0 Then
    Range("Week3").EntireRow.Hidden = False
Else
    Range("Week3").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "4") > 0 Then
    Range("Week4").EntireRow.Hidden = False
Else
    Range("Week4").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "5") > 0 Then
    Range("Week5").EntireRow.Hidden = False
Else
    Range("Week5").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "6") > 0 Then
    Range("Week6").EntireRow.Hidden = False
Else
    Range("Week6").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "7") > 0 Then
    Range("Week7").EntireRow.Hidden = False
Else
    Range("Week7").EntireRow.Hidden = True
End If

If InStr(1, ActiveSheet.Range("J2").Value, "8") > 0 Then
    Range("Week8").EntireRow.Hidden = False
Else
    Range("Week8").EntireRow.Hidden = True
End If

End Sub

The complaint from teachers using this is that they get confused if they have to keep clicking the "Show" button on each sheet in turn.

I've been struggling to find a way to make one button, on the first sheet, operate the macro to hide the appropriate rows on all sheets in the workbook.

Does anyone have any ideas?

Cheers,
J

 

[pbornemeier]

If J2 on a student worksheet is blank then it will use the value of J@ from the summary worksheet

Option Explicit

Sub ShowWeekBasedOnJ2ValueOfEachWorksheet()

    Dim lWeekIndex As Long
    Dim wks As Worksheet
    Dim lWeekValue As Long
    
    For Each wks In ThisWorkbook.Worksheets
        Select Case wks.Name
        Case "Summary", "Contact"  'Enter names of all non-student sheets here
            'Do nothing
        Case Else
            'Remaining wks should be student sheets
            
            'If the week to show on wks is blank, get the value from the summary sheet
            If wks.Range("J2").Value = "" Then
                lWeekValue = Worksheets("Summary").Range("J2").Value
            Else
                lWeekValue = wks.Range("J2").Value
            End If
            
            'Hide/Unhide ranges on wks
            For lWeekIndex = 1 To 8
                If InStr(1, lWeekValue, lWeekIndex) > 0 Then
                    wks.Range("Week" & lWeekIndex).EntireRow.Hidden = False
                Else
                    wks.Range("Week" & lWeekIndex).EntireRow.Hidden = True
                End If
            Next
        End Select
    Next
End Sub

 

[JSpaced]

Wow, Phil, that's incredible. The J2 cell in each worksheet pulls from the "WeekNo" named cell on the Setup sheet, so it will always be populated (in theory).
I'm going to experiment with this, but does it go into the VBA for each sheet, or just the workbook?
Many thanks,
J

 

[JSpaced]

@pbornemeier Hey Phil, this is working brilliantly! Thank you. J