Hide selective sheets "veryhidden"

[Jonsson]

Hi,

I have record this code to get certain sheets hidden. The WB contains
lot of sheets and I only want to hide some of them.

The code works great as long I dont change it to "xlSheetVeryHidden"

The error appears when reaching "ActiveWindow.SelectedSheets.Visible
xlSheetVeryHidden

Whats wrong, and is it possible to write code thats not so ugly as thi
one?

Any help is appreciated

//Thomas

Sub Hide ()

Sheets("V25").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=25
Sheets(Array("V25", "V26", "V27", "V28", "V29", "V30", "V31"
"V32", "V33", "V34", "V35", _
"V36", "V37", "V38", "V39", "V40", "V41", "V42", "V43", "V44"
"V45", "V46", "V47", "V48", _
"V49")).Select
Sheets("V25").Activate
ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden

Sheets("V25U").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("V25U", "V26U", "V27U", "V28U", "V29U", "V30U"
"V31U", "V32U", "V33U", _
"V34U", "V35U", "V36U", "V37U", "V38U", "V39U", "V40U", "V41U"
"V42U", "V43U", "V44U", _
"V45U", "V46U", "V47U", "V48U", "V49U")).Select
Sheets("V25U").Activate
Sheets(Array("V50U", "V51U", "V52U", "V53U")).Selec
Replace:=False
ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden
End Su

 

[Frank Kabel]

Hi
try something like
dim wks
for each wks in activewindow.selectedsheets
wks.Visible = xlSheetVeryHidden
next

 

[Jonsson]

Hi,

What I understand the suggested code loops through all the sheets i
the workbook and hide them all.

What I need to do is select about 50 sheets from a wb that contains
total of 200 sheets. The code I recorded works great as long I choose
"ActiveWindow. Select. Visible= False or xlSheetHidden"

What I can't understand is why the code failure when changing t
"xlSheetVeryHidden".

Any ideas?


//Thoma

 

[Dave Peterson]

If the names don't have a pattern, you could just code all the names into your
sub:

Option Explicit
Sub Hide1()

Dim myArr1 As Variant
Dim myArr2 As Variant
Dim iCtr As Long

myArr1 = Array("V25", "V26", "V27", "V28", "V29", "V30", "V31", _
"V32", "V33", "V34", "V35", "V36", "V37", "V38", _
"V39", "V40", "V41", "V42", "V43", "V44", "V45", _
"V46", "V47", "V48", "V49")


myArr2 = Array("V25U", "V26U", "V27U", "V28U", "V29U", "V30U", _
"V31U", "V32U", "V33U", "V34U", "V35U", "V36U", _
"V37U", "V38U", "V39U", "V40U", "V41U", "V42U", _
"V43U", "V44U", "V45U", "V46U", "V47U", "V48U", _
"V49U", "V50U", "V51U", "V52U", "V53U")

For iCtr = LBound(myArr1) To UBound(myArr1)
Worksheets(myArr1(iCtr)).Visible = xlSheetVeryHidden
Next iCtr

For iCtr = LBound(myArr2) To UBound(myArr2)
Worksheets(myArr2(iCtr)).Visible = xlSheetVeryHidden
Next iCtr

End Sub

But if the names are consistent, you could use that:

Sub Hide2()

Dim iCtr As Long

For iCtr = 25 To 49
Worksheets("V" & Format(iCtr, "00")).Visible = xlSheetVeryHidden
Next iCtr

For iCtr = 25 To 53
Worksheets("V" & Format(iCtr, "00") & "U").Visible = xlSheetVeryHidden
Next iCtr

End Sub

Remember that there has to be at least one sheet visible.

 

[Dave Peterson]

And there's a difference between the properties for one worksheet and a group of
worksheets.

Why? Heck if I know!

 

[Jonsson]

Dave!

I appreciate your help!!

You really solved my problem, just what I needed!!
I get along fine with "Hide2" macro, nice.

Best wishes!

//Thoma