Hide sheets

[kclong]

I saw this on the message board, which works to hide all sheets but Sheet1. How do I modify it to hide all sheets except Sheet 1 and Sheet2?

Dim sht As Object

For Each sht In Sheets
If sht.Name <> "Sheet1" Then
sht.Visible = xlSheetHidden
End If
Next sht

 

[ProfessorPorcupine]

Try this code, I think it is a little better than what you have and will keep Sheet1 (or any other sheet). Note you can either choose hidden, or very hidden - I typically use very hidden so the user can't get to the sheet except in the VBA editor.

Dim wksCurrent As Worksheet
Const conKeepSheet As String = "Sheet1"

For Each wksCurrent In Worksheets
If wksCurrent.Name <> conKeepSheet Then
wksCurrent.Visible = xlSheetVeryHidden
End If
Next

 

[kclong]

won't this hide all sheets except sheet1? I'd like to be able to see sheet1 and sheet2.

 

[ProfessorPorcupine]

Just change the following for keeping two sheets.

Const conKeepSheet1 As String = "Sheet1"
Const conKeepSheet2 As String = "Sheet2"

If wksCurrent.Name <> conKeepSheet1 or wksCurrent.Name <> conKeepSheet2 Then

 

[ProfessorPorcupine]

Just change the following for the keeping two sheets.

Const conKeepSheet1 As String = "Sheet1"
Const conKeepSheet2 As String = "Sheet2"

If wksCurrent.Name <> conKeepSheet1 or wksCurrent.Name <> conKeepSheet2 Then

Note it is better to have the sheet names at the top of your code as constants to make them easy to find and so you would only have to change them once if the names of the sheets changed.

 

[BeeKay]

Hi All,

First post! Apologies for the NecroThread!

Very new to VBA & I'm trying to create a macros for pretty much the same thing as above, however I need to keep three sheets open - see code below - It throws up an error at the"wksCurrent.Visible = xlSheetVeryHidden" line. ("Run-time error '1004': Method 'Visible' of object '_Worksheet' failed")

Have I left something off?

Thanks in advance

BK

Code:

Sub Shutdown_01()
Dim wksCurrent As Worksheet
Const conKeepSheet1 As String = "Approx POs"
Const conKeepSheet2 As String = "BQ"
Const conKeepSheet3 As String = "Commercials"

For Each wksCurrent In Worksheets
If wksCurrent.Name <> conKeepSheet1 Or wksCurrent.Name <> conKeepSheet2 Or wksCurrent.Name <> conKeepSheet3 Then
wksCurrent.Visible = xlSheetVeryHidden
End If
Next

Sheets("Commercials").Activate 'To Activate
Sheets("Commercials").Range("I27").Activate 'Activate Cell


End Sub

 

[ProfessorPorcupine]

Possibly you need to qualify which worksheets you are referring to, try changing it to

[COLOR=#333333]For Each wksCurrent In [/COLOR]ThisWorkbook.Worksheets

 

[dmt32]

Hi welcome to forum,

Try this code & see if does what you want:

Sub Shutdown_01()
    Dim wksCurrent As Worksheet
    Dim m As Variant

    For Each wksCurrent In ThisWorkBook.Worksheets
        M = Application.Match(wksCurrent.Name, Array("Approx POs", "BQ", "Commercials"), False)
        wksCurrent.Visible = IIf(IsError(M), xlSheetVeryHidden, xlSheetVisible)
    Next wksCurrent
    
     With ThisWorkBook.WorkSheets("Commercials")
        .Activate 'To Activate
        .Range("I27").Activate 'Activate Cell
     End With


End Sub

A tip for future, rather than step in to an old thread, start your own & provide a link to any relevant thread. This way, you are likely to get more responses.

Hope helpful

Dave

 

[BeeKay]

Hi welcome to forum,

Try this code & see if does what you want:

Sub Shutdown_01()
    Dim wksCurrent As Worksheet
    Dim m As Variant

    For Each wksCurrent In ThisWorkBook.Worksheets
        M = Application.Match(wksCurrent.Name, Array("Approx POs", "BQ", "Commercials"), False)
        wksCurrent.Visible = IIf(IsError(M), xlSheetVeryHidden, xlSheetVisible)
    Next wksCurrent
    
     With ThisWorkBook.WorkSheets("Commercials")
        .Activate 'To Activate
        .Range("I27").Activate 'Activate Cell
     End With


End Sub

A tip for future, rather than step in to an old thread, start your own & provide a link to any relevant thread. This way, you are likely to get more responses.

Hope helpful

Dave

Dave,

This worked beautifully - Thank you.

Noted re starting new thread.

Cheers!

 

[BeeKay]

Possibly you need to qualify which worksheets you are referring to, try changing it to

[COLOR=#333333]For Each wksCurrent In [/COLOR]ThisWorkbook.Worksheets

Thanks for your help PP!