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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|