A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hide selective sheets "veryhidden"



 
 
Thread Tools Display Modes
  #1  
Old June 14th, 2004, 12:01 PM
Jonsson
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

Hi,

I have record this code to get certain sheets hidden. The WB contains a
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 this
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")).Select
Replace:=False
ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden
End Sub


---
Message posted from http://www.ExcelForum.com/

  #2  
Old June 14th, 2004, 01:54 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hi,

I have record this code to get certain sheets hidden. The WB contains
a 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
this 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")).Select
Replace:=False
ActiveWindow.SelectedSheets.Visible = xlSheetVeryHidden
End Sub


---
Message posted from http://www.ExcelForum.com/


  #3  
Old June 14th, 2004, 11:56 PM
Jonsson
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

Hi,

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

What I need to do is select about 50 sheets from a wb that contains a
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 to
"xlSheetVeryHidden".

Any ideas?


//Thomas


---
Message posted from http://www.ExcelForum.com/

  #4  
Old June 15th, 2004, 01:40 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

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.



"Jonsson " wrote:

Hi,

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

What I need to do is select about 50 sheets from a wb that contains a
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 to
"xlSheetVeryHidden".

Any ideas?

//Thomas

---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

  #5  
Old June 15th, 2004, 01:41 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

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

Why? Heck if I know!



"Jonsson " wrote:

Hi,

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

What I need to do is select about 50 sheets from a wb that contains a
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 to
"xlSheetVeryHidden".

Any ideas?

//Thomas

---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

  #6  
Old June 15th, 2004, 08:29 PM
Jonsson
external usenet poster
 
Posts: n/a
Default Hide selective sheets "veryhidden"

Dave!

I appreciate your help!!

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

Best wishes!

//Thomas


---
Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.