Hiding and Unhiding two sets of worksheets

My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.

If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target.Address <> "$D$8" Then Exit Sub
    Select Case Target.Value
        Case 1
            Sheets("1-Sheet1").Visible = xlSheetVisible
            Sheets("2-Sheet1").Visible = xlSheetHidden
            Sheets("2-Sheet2").Visible = xlSheetHidden
            Sheets("3-Sheet1").Visible = xlSheetHidden
            Sheets("3-Sheet2").Visible = xlSheetHidden
            Sheets("3-Sheet3").Visible = xlSheetHidden

        Case 2
            Sheets("1-Sheet1").Visible = xlSheetHidden
            Sheets("2-Sheet1").Visible = xlSheetVisible
            Sheets("2-Sheet2").Visible = xlSheetVisible
            Sheets("3-Sheet1").Visible = xlSheetHidden
            Sheets("3-Sheet2").Visible = xlSheetHidden
            Sheets("3-Sheet3").Visible = xlSheetHidden

        Case 3
            Sheets("1-Sheet1").Visible = xlSheetHidden
            Sheets("2-Sheet1").Visible = xlSheetHidden
            Sheets("2-Sheet2").Visible = xlSheetHidden
            Sheets("3-Sheet1").Visible = xlSheetVisible
            Sheets("3-Sheet2").Visible = xlSheetVisible
            Sheets("3-Sheet3").Visible = xlSheetVisible

        Case Else
    End Select
End Sub

The code is OK when I have 36 Worksheets and link this code to one drop down menu. When there are 72 Worksheets it fails. There must be a way to shorten the commands but

Sheets(Array("2-Sheet1", "2-Sheet2")).Visible = True

Does not work whereas

Sheets(Array("2-Sheet1", "2-Sheet2")).Visible = False

does!

This question is linked to my previous post :-

Can I attach a macro to a drop down list selection

Regards

Geoff

Hi,

If you can split your code in two separate macros, then you can try this:

In the ListBox_Click event or ComboBox_Change event (depending on which one you are using as a dropdown), check for the Text property and run the relevant macro accordingly. Here is a sample code:

Private Sub ListBox1_Click()
If ListBox1.Text = "ABC" Then
  Application.Run ("macro1")
Else
  Application.Run ("macro2")
End If
End Sub

Regards.

Thanks very much for the responses from SE1429 and Martesoft. I was having trouble with a ComboBox so tried MarteSoft's idea and understanding it almost fully implemented it successfully, until I came across the inbuilt limit of 60 arguments for a procedure. Unfortunately I need 72.

Microsoft Help tells me that I can do this with an array "You can also pass multiple arguments by placing them in an array"

My working code is as follows with 36 Worksheets, but I need to add another set of 36 Worksheets in exactly the same format as the first 36.

How can I implement an array to get round the 60 argument limit?

Thanks again for all the ideas.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target.Address <> "$F$8" Then Exit Sub
    
    Select Case Target.Value
        Case "1-Sheet1"
            SheetVisibility _
            xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden

            
        Case "2-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden

            
        Case "3-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden
            
            
        Case "4-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden

            
        Case "5-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden
        
        
        
        Case "6-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden
        
        Case "7-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden
        
        
        Case "8-Sheet"
            SheetVisibility _
            xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, xlSheetHidden, _
            xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible, xlSheetVisible

Private Sub SheetVisibility(sh1 As Long, sh2 As Long, sh3 As Long, sh4 As Long, sh5 As Long, _
                            sh6 As Long, sh7 As Long, sh8 As Long, sh9 As Long, sh10 As Long, _
                            sh11 As Long, sh12 As Long, sh13 As Long, sh14 As Long, sh15 As Long, _
                            sh16 As Long, sh17 As Long, sh18 As Long, sh19 As Long, sh20 As Long, _
                            sh21 As Long, sh22 As Long, sh23 As Long, sh24 As Long, sh25 As Long, _
                            sh26 As Long, sh27 As Long, sh28 As Long, sh29 As Long, sh30 As Long, _
                            sh31 As Long, sh32 As Long, sh33 As Long, sh34 As Long, sh35 As Long, _
                            sh36 As Long)

    Sheets("1-Sheet1").Visible = sh1
    Sheets("2-Sheet1").Visible = sh2
    Sheets("2-Sheet2").Visible = sh3
    Sheets("3-Sheet1").Visible = sh4
    Sheets("3-Sheet2").Visible = sh5
    Sheets("3-Sheet3").Visible = sh6
    Sheets("4-Sheet1").Visible = sh7
    Sheets("4-Sheet2").Visible = sh8
    Sheets("4-Sheet3").Visible = sh9
    Sheets("4-Sheet4").Visible = sh10
    Sheets("5-Sheet1").Visible = sh11
    Sheets("5-Sheet2").Visible = sh12
    Sheets("5-Sheet3").Visible = sh13
    Sheets("5-Sheet4").Visible = sh14
    Sheets("5-Sheet5").Visible = sh15
    Sheets("6-Sheet1").Visible = sh16
    Sheets("6-Sheet2").Visible = sh17
    Sheets("6-Sheet3").Visible = sh18
    Sheets("6-Sheet4").Visible = sh19
    Sheets("6-Sheet5").Visible = sh20
    Sheets("6-Sheet6").Visible = sh21
    Sheets("7-Sheet1").Visible = sh22
    Sheets("7-Sheet2").Visible = sh23
    Sheets("7-Sheet3").Visible = sh24
    Sheets("7-Sheet4").Visible = sh25
    Sheets("7-Sheet5").Visible = sh26
    Sheets("7-Sheet6").Visible = sh27
    Sheets("7-Sheet7").Visible = sh28
    Sheets("8-Sheet1").Visible = sh29
    Sheets("8-Sheet2").Visible = sh30
    Sheets("8-Sheet3").Visible = sh31
    Sheets("8-Sheet4").Visible = sh32
    Sheets("8-Sheet5").Visible = sh33
    Sheets("8-Sheet6").Visible = sh34
    Sheets("8-Sheet7").Visible = sh35
    Sheets("8-Sheet8").Visible = sh36

End Sub

I would go with this approach.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target.Address <> "$F$8" Then Exit Sub
    
    Select Case Target.Value
        Case "1-Sheet1"
            SheetVisibility 1
        Case "2-Sheet"
            SheetVisibility 2
        Case "3-Sheet"
            SheetVisibility 3
        Case "4-Sheet"
            SheetVisibility 4
        Case "5-Sheet"
            SheetVisibility 5
        Case "6-Sheet"
            SheetVisibility 6
        Case "7-Sheet"
            SheetVisibility 7
        Case "8-Sheet"
            SheetVisibility 8
        End Select

End Sub

Sub SheetVisibility(Item As Long)

    Dim lngIndex As Long
    Dim lngItem As Long
    
    For lngIndex = 1 To Item
        Sheets(Item & "-Sheet" & lngIndex).Visible = xlSheetVisible
    Next
    
    For lngItem = 1 To 8
        If lngItem = Item Then
            ' already done
        Else
            For lngIndex = 1 To lngItem
                Sheets(lngItem & "-Sheet" & lngIndex).Visible = xlSheetHidden
            Next
        End If
    Next
    
End Sub

Andy,

Thank you so much, that is so much simpler!!

Your code tackles the first 36 of my worksheets, but the next 36 are Main1 to Main8, Main1 being 1 worksheet, Main2 being 2 worksheets etc.

I would also like to have a Case showing all the Sheets, a Case showing all the Mains and a Case hiding all the worksheets.

It just amazes me how many different ways of doing the same thing there are!

Thanks again