Hiding sheets

[eggtimer]

Hi

i have the below code, (its a bit larger than this, but this should be enough to show my problem)

i have a workbook with around 30 sheets , about 20 sheets contain personal info and are hidden, for the managers that need to see tho sheets i have given them personal macros as below, one to hide and lock and another to unhide

my problem is, historic sheets need to be opened and as they have agents that no longer exist the macros crash, i have used 'on error' as below, but that only seems to work once

can anybody show me what i am doing wrong or suggest a better alternative method

thanks

Wayne

Sub closesheets()
On Error GoTo Jan19
ActiveWorkbook.Unprotect Password:="ShouldIWorrydAboutCovid20?"
    Sheets("Tom H").Visible = False
    Sheets("Mark S").Visible = False
    Sheets("Jane P").Visible = False
    Sheets("Scott R").Visible = False
    Sheets("Sarah K").Visible = False
    Sheets("Steve F").Visible = False
    Sheets("Geoff T").Visible = False
    Sheets("Matt D").Visible = False
    ActiveWorkbook.Protect Password:="ShouldIWorrydAboutCovid20?"
    Exit Sub
Jan19:
On Error GoTo May18
ActiveWorkbook.Unprotect Password:="ShouldIWorrydAboutCovid20?"
    Sheets("Caroline B").Visible = False
    Sheets("Tom H").Visible = False
    Sheets("Mark S").Visible = False
    Sheets("Jane P").Visible = False
    Sheets("Andrew F").Visible = False
    Sheets("Sarah K").Visible = False
    Sheets("Peter B").Visible = False
    ActiveWorkbook.Protect Password:="ShouldIWorrydAboutCovid20?"
    Exit Sub
May18:
On Error GoTo Feb17
ActiveWorkbook.Unprotect Password:="ShouldIWorrydAboutCovid20?"
    Sheets("Caroline B").Visible = False
    Sheets("Mike G").Visible = False
    Sheets("Mark S").Visible = False
    Sheets("Jane P").Visible = False
    Sheets("Andrew F").Visible = False
    Sheets("Peter B").Visible = False
    ActiveWorkbook.Protect Password:="ShouldIWorrydAboutCovid20?"
    Exit Sub

 

[Fluff]

How about

Sub eggtimer()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Tom H", "Mark S", "Jane P", "Scott R")
   ActiveWorkbook.Unprotect Password:="ShouldIWorrydAboutCovid20?"
   For i = 0 To UBound(Ary)
      If Evaluate("isref('" & Ary(i) & "'!A1)") Then
         Sheets(Ary(i)).Visible = False
      End If
   Next i
   ActiveWorkbook.Protect Password:="ShouldIWorrydAboutCovid20?"
End Sub

Just add the rest of the sheet names to the array

 

[eggtimer]

Thanks Fluff

I have no idea what it is is doing, but I'll give it a go

am i right to assume that to reverse it is is just "visible = true"

cheers

 

[Fluff]

Yup, that's right?

 

[eggtimer]

It works perfectly, thank you

you have saved me a lot of time

i assume it is looking for Cell A1 in those sheets, so if the sheet is not there the cell is not there, is that correct?

 

[Fluff]

That's about right.