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

 

[Maverick99]

Erik,

I tried yours out and it errored at the .select part of the code.

I even started to get an error with JonMo's when there was one sheet left. When I inserted "On Error Resume Next" prior to the LBound and UBound statements, then it just left one sheet and carried on. I'll have to and manually delete the one sheet after performing actions on the rest.

Thanks for your input though,

Maverick

 

[erik.van.geit]

it works for me
if it errors then you have some sheetnames in the array which doesn't exist
but, as said, I only wanted to add a viewpoint when posting my code

you might get more inspiration and knowledge by looking at
this thread about worksheets stuff

 

[jindon]

Also
If any sheet(s) is/are hidden, you can not Select.

 

[erik.van.geit]

Also
If any sheet(s) is/are hidden, you can not Select.

Hi, jindon,

Long time ago we were in same thread
the code has

    For Each sht In ThisWorkbook.Sheets
    sht.Visible = True
    Next sht

so unless Maverick99 has changed the code, this should not be the problem

kind regards,
Erik

 

[jindon]

Hi Erik,

It's been a while.
Right, I missed that part.
OP should show us the code that doesn't work.

 

[erik.van.geit]

YES, jindon!!

Maverick99, you should try this out in a little sample workbook - no data needed - with "easy sheetnames", so you can check if you get it to work.

If not tell us what you are working on and what code you are using.

best regards,
Erik

 

[Maverick99]

Erik,

Sorry it has taken so long to get back. This is the code that I tested in a new workbook.

Option Explicit
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("Sheet1 ", "Sheet2", "Sheet3 ", "Sheet4 "))
ThisWorkbook.Sheets(aBudget).Select
ActiveWindow.SelectedSheets.Visible = False
 
Application.ScreenUpdating = True
 
End Sub

I have 5 sheets 1-5. The sht.Visible does work. However, the aBudget provides the same Run-time error #9 (Subscript out of Range).

I have tried the array with and with the ( ) surrounding the entire statement. They both end up with the same result.

Do you have any ideas as to why this could be occuring?

Maverick

 

[Jonmo1]

Check the spelling of your sheet names, they need to be EXACT. Looks like you have trailing spaces on a couple of them...

aBudget = (Array("Sheet1 ", "Sheet2", "Sheet3 ", "Sheet4 "))

 

[Maverick99]

JonMo,

You're right. I did have some trailing spaces and when fixed, it worked.

Thanks,

Maverick

 

[sunwf01]

Hi can I find out how to hide an array of sheets that are open as well as all sheets except for 2 that are open? Eg. Sheet1 -Sheet3 are open. I want to hide them. I know I can easily record a macro to hide them but if subsequently Sheet4-Sheet5 are open can I assign the same macro to also hide them? How do I write the VBA macro code? I'm not very experienced in VBA so hope you will all be patient with my question. Thanks.