0

I want to hide all tabs whose names are "ABC", "DEF", "GHI"

This is my code:

Sub hideSheets()
Dim ws As Worksheet
invisible = Array("ABC", "DEF", "GHI")

For Each ws In ActiveWorkbook.Worksheets
    For Each nome In invisible
        If ws.Name = nome Then
            ws.Visible = False
        Else
            ws.Visible = True
        End If
    Next nome

Next ws

End Sub

Which isn't hiding any sheet.

Improve this question
4
  • This has been answered here. You need to use: xlSheetVeryHidden, xlSheetHidden or xlSheetVisible after the = instead of true or false. Commented Oct 14, 2015 at 16:31
  • @ScottCraner That question's answers do not address the array component in the title of the question. In addition your comment, while not wrong, is misleading. xlSheetHidden = 0, which is exactly equivalent to False , xlSheetVisible = -1 which is exactly equivalent to True. So those two visible states can and often are achieved by using the simple Boolean values. xlSheetVeryHidden = 2 which can be set either with the constant or the literal or an expression. In any case, nothing from that other question addresses this question. Commented Oct 14, 2015 at 17:00
  • @ExcelHero As always, I bow to your knowledge. Commented Oct 14, 2015 at 17:03
  • @ScottCraner :) Well no need for that, but the more knowledge we share the better everyone becomes! Commented Oct 14, 2015 at 17:05

2 Answers 2

Reset to default
3

To hide the sheets in your invisible array, it is as simple as this:

Sheets(invisible).Visible = False

Note: this works well for hiding. To make the sheets visible again, you must set each sheet visible one by one; you cannot use the array in one line like you can for hiding.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

LOL! this is really simple!
Any other simple way of unhiding anything that isn't in that array?
Nope. Unhiding requires doing one sheet at a time.
Ok, I've unhidden all the sheets one by one and then hidden the ones in the array. Thanks a lot!
Thanks @NunoNogueira.
1

Take out the else. It hides it but on the next loop, it unhides again.

Dim ws As Worksheet
invisible = Array("ABC", "DEF", "GHI")

For Each ws In ActiveWorkbook.WorkSheets

    For Each nome In invisible
        If ws.name = nome Then
            ws.Visible = False
        End If
    Next nome

Next ws
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.