Hiding/Unhiding worksheets depending on selected worksheet

[mr_d79]

Hi,

I am very new to VB Code and I am piecing it together as I go. I have a workbook that contains 70 separate worksheets. I am trying to organize the worksheets so that certain worksheets unhide when I click a specific worksheet and then get certain worksheets to hide and unhide when a different (specific) worksheet is selected. Is this possible within excel? Any help would be greatly appreciated.

Sincerely,

Tom

The code I created so far is:

Sub Worksheet_Activate()

For Each ws In ThisWorkbook.Worksheets

If ActiveSheet.Name = "Kindergarten" Then
Worksheets("Sheet8").Visible = True
Worksheets("Sheet9").Visible = True
Worksheets("Sheet10").Visible = False
Worksheets("Sheet11").Visible = False
Worksheets("Sheet12").Visible = False
Worksheets("Sheet13").Visible = False
Worksheets("Sheet14").Visible = False
Worksheets("Sheet15").Visible = False
Worksheets("Sheet16").Visible = False
Worksheets("Sheet17").Visible = False
Worksheets("Sheet18").Visible = False
Worksheets("Sheet19").Visible = False
Worksheets("Sheet20").Visible = False

If ActiveSheet.Name = "1st Grade" Then
Worksheets("Sheet8").Visible = False
Worksheets("Sheet9").Visible = False
Worksheets("Sheet10").Visible = True
Worksheets("Sheet11").Visible = True
Worksheets("Sheet12").Visible = False
Worksheets("Sheet13").Visible = False
Worksheets("Sheet14").Visible = False
Worksheets("Sheet15").Visible = False
Worksheets("Sheet16").Visible = False
Worksheets("Sheet17").Visible = False
Worksheets("Sheet18").Visible = False
Worksheets("Sheet19").Visible = False
Worksheets("Sheet20").Visible = False

End If
End If

Next ws

End Sub

This code works as long as you select the VB editor and run the program with the desired sheet active, but it will not run when clicking between the "Kindergarten" worksheet and then on the "1st Grade" worksheet.

 

[AC PORTA VIA]

your code needs to go in sheet Kindergarten module and sheet 1st Grade module

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
   Select Case Sh.Name
      Case "Kindergarten"
         Worksheets("Sheet8").Visible = True
         Worksheets("Sheet9").Visible = True
         Worksheets("Sheet10").Visible = False
         Worksheets("Sheet11").Visible = False
         Worksheets("Sheet12").Visible = False
         Worksheets("Sheet13").Visible = False
         Worksheets("Sheet14").Visible = False
         Worksheets("Sheet15").Visible = False
         Worksheets("Sheet16").Visible = False
         Worksheets("Sheet17").Visible = False
         Worksheets("Sheet18").Visible = False
         Worksheets("Sheet19").Visible = False
         Worksheets("Sheet20").Visible = False
      Case "1st Grade"
         Worksheets("Sheet8").Visible = False
         Worksheets("Sheet9").Visible = False
         Worksheets("Sheet10").Visible = True
         Worksheets("Sheet11").Visible = True
         Worksheets("Sheet12").Visible = False
         Worksheets("Sheet13").Visible = False
         Worksheets("Sheet14").Visible = False
         Worksheets("Sheet15").Visible = False
         Worksheets("Sheet16").Visible = False
         Worksheets("Sheet17").Visible = False
         Worksheets("Sheet18").Visible = False
         Worksheets("Sheet19").Visible = False
         Worksheets("Sheet20").Visible = False
   End Select
End Sub

This needs to go in the ThisWorkbook module.

 

[mr_d79]

Wow!! Thank you so much! This is the perfect solution. Now all I have to do is scale this up to the 70 sheets and it will take care of my school's needs. Thank you so much for the help. This is an amazing community and I am so glad to be a part of it.

 

[Fluff]

Glad we could help & thanks for the feedback.