Correct Syntax to Hide Multiple Sheets

[Maverick99]

I have over 60 sheets within a workbook. There are some sheets that I want to hide or unhide depending upon the macro. I have the MR and searched in several areas but keep coming up blank with how to either select or hide these sheets.

This is from the MR:

Sheets(Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", Summary_ Monthly")).Select

When I try to use this in the code it errors out. I'd appreciate any help with this.

Thanks,

Maverick

 

[Norie]

Maverick

What's the MR?

Are you sure that's all the code?

Because I don't see a lot of hiding going on.

 

[Maverick99]

The MR is the Macro Recorder. I haven't tried to hide anything yet as the complier won't even let me select the sheets. I figured if I could get the macro to select these sheets then I could work on the .visible = False.

Maverick

 

[Norie]

So what sheets do you want hide?

Is there some criteria, say sheet name?

 

[Maverick99]

All of the sheets listed, which are just a subset of the 60+ that's in the workbook, I want to hide. Both the Macro Recorder and Microsoft says to use this syntax, but it doesn't seem to work.

Maverick

 

[Jonmo1]

I'll assume since you have over 60 sheets in the book, you probably want to hide all but a select few? It's much easier to specify the ones you DON'T Want to hide, try this

'List all sheets you DO NOT WANT hidden here..
MySheets = Array("DON'T HIDE ME","This One Either")
 
'First you need to UNHIDE all sheets specified above, at least 1 sheet MUST be visible at all times
For x = lbound(MySheets) to Ubound(MySheets)
    Sheets(x).Visible = True
Next x
 
'Now Hide all Others.
For each ws in Sheets
    x = Application.match(ws.Name,MySheets,0)
    If IsError(x) Then
        ws.Visible = False
    End If
Next ws

 

[Maverick99]

I guess I must have missed something, or didn't understand your use of 'x'.

It did run through up until the Sheets(x).Visible=True

Dim aBudget As Variant
Dim x As Variant
 
aBudget = (Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", "Summary_Monthly"))
For x = LBound(aBudget) To UBound(aBudget)
    Sheets(x).Visible = True
    Next x

I apologize for fully understanding how examples are coded.

Maverick

 

[Jonmo1]

my bad, forgot a small bit

IMPORTANT
this part
aBudget = Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", "Summary_Monthly")

This is the list of sheets you DO NOT WANT HIDDEN

Dim x As Variant
Dim aBudget As Variant
Dim ws As Worksheet
 
'List all sheets you DO NOT WANT hidden here..
aBudget = Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)",  "DataRef", "Dept Summary New", "Summary_Dept", "Summary_Monthly")
 
'First you need to UNHIDE all sheets specified above, at least 1 sheet MUST be visible at all times
For x = LBound(aBudget) To UBound(aBudget)
    Sheets(aBudget(x)).Visible = True
Next x
 
'Now Hide all Others.
For Each ws In Sheets
    x = Application.Match(ws.Name, aBudget, 0)
    If IsError(x) Then
        ws.Visible = False
    End If
Next ws

 

[Maverick99]

JonMo,

Excellent, I just learned something a little more. Thanks for your insight.

Maverick

 

[erik.van.geit]

Hi,

A post only to add some other viewpoint...
In case you would like to hide the sheets from the list, then you can avoid the loop
ThisWorkbook.Sheets(aBudget).Select
ActiveWindow.SelectedSheets.Visible = False

Sub test()
 
Dim sht As Variant
Dim aBudget As Variant
 
Application.ScreenUpdating = False
 
    For Each sht In ThisWorkbook.Sheets
    sht.Visible = True
    Next sht
 
aBudget = Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", "Summary_Monthly")
ThisWorkbook.Sheets(aBudget).Select
ActiveWindow.SelectedSheets.Visible = False
 
Application.ScreenUpdating = True
 
End Sub

kind regards,
Erik