hide sheets based on activate sheet

[MKLAQ]

hello
I try making macro by hide specific sheets based on activate sheet. so if the sheet("data") is activate then just hide sh1,2,3 and show the rest of sheets sh4,5,6 and if the sheet("main") is activate then show sheets sh1,2,3 and hide sh4,5,6.
this is my trying but not work

Sub vv()
Dim ws As Worksheet
Dim sh, sh1 As Variant
sh = Array("sh1", "sh2", "sh3")
sh1 = Array("sh4", "sh5", "sh6")
For Each ws In Worksheets
   If Sheets("data").Activate Then
      sh.Visible = False
   End If

If Sheets("main").Activate Then
      sh1.Visible = False
         End If
Next ws
End Sub

 

[Max1616]

Hello,

Your best bet here would be to add some code into the worksheet module and use code like this:

Private Sub Worksheet_Activate()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Sheet3" Or ws.Name = "Sheet4" Or ws.Name = "Sheet5" Then
        ws.Visible = xlSheetHidden
    Else
        ws.Visible = xlSheetVisible
    End If

Next

End Sub

Add this code to both the "data" sheet module, and "main" sheet module, then edit the sheet names for you want visible.

 

[MKLAQ]

great ! can I put into array instead of using or every time . I try this

If ws.Name = Array("sh4", "sh5", "sh6")

but doesn't work

 

[Max1616]

It's a tad bit more complicated if you'd like to use an array. Without creating a function, I would try something like this:

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim i As Integer
Dim arr As Variant
Dim SheetList As String

arr = Array("Sheet3", "Sheet4", "Sheet5")

    For i = LBound(arr) To UBound(arr)
        SheetList = SheetList & "," & arr(i)
    Next i
    
SheetList = Mid(SheetList, 2, 10000)

For Each ws In ActiveWorkbook.Worksheets

     If InStr(SheetList, ws.Name) Then
         ws.Visible = xlSheetHidden
     Else
         ws.Visible = xlSheetVisible
     End If

Next

End Sub

 

[MKLAQ]

brilliant ! many thanks for your help