Hide all worksheets (array) if unhidden

[sdoppke]

Hi All, i have this code, which seems like it should work, but its not... and its getting the best of me. I am trying to hide all worksheets except for a few in an array, if they are not allready hidden. here is what i have

Sub hidebut()
Dim ws As Worksheet, wb As Workbook
For Each ws In wb.Sheets
    If ws.Name <> Sheets(Array("Dashboard", "MyStoreInfo")) Then
        ws.Visible = False
    Else
        ws.Visible = True
Next
End Sub

Anyone know where im going wrong?

sd

 

[tweedle]

I'm not sure why you're putting the effort into an array of 2;

Sub hidebut()
Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook
 
For Each ws In wb.Sheets
    If (ws.Name <> "Dashboard") And _
    (ws.Name <> "MyStoreInfo") Then
        ws.Visible = False
    Else
        ws.Visible = True
    End If
Next
End Sub

 

[sdoppke]

I'm not sure why you're putting the effort into an array of 2;

Sub hidebut()
Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook
 
For Each ws In wb.Sheets
    If (ws.Name <> "Dashboard") And _
    (ws.Name <> "MyStoreInfo") Then
        ws.Visible = False
    Else
        ws.Visible = True
    End If
Next
End Sub

Thank you that worked great, 2 was a bad example, sometime there are 3. Could you tell me what to modify to make it leave just 1 sheet?

Thanks again for your help!

sd

 

[sdoppke]

i played with it and think this is it?

Sub Store_Info()
Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
 For Each ws In wb.Sheets
    If (ws.Name <> "MyStoreInfo") Then
        ws.Visible = False
    Else
        ws.Visible = True
    End If
Next
Sheets("MyStoreInfo").Select
Range("C2").Select
Application.ScreenUpdating = True
End Sub

 

[sdoppke]

i played with it and think this is it?

Sub Store_Info()
Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
 For Each ws In wb.Sheets
    If (ws.Name <> "MyStoreInfo") Then
        ws.Visible = False
    Else
        ws.Visible = True
    End If
Next
Sheets("MyStoreInfo").Select
Range("C2").Select
Application.ScreenUpdating = True
End Sub

No Actually that doesnt work for just one sheet ("MyStoreInfo")

Any ideas?

sd

 

[sdoppke]

ok looks like the problem im having is when i run the macro and the:

For Each ws In wb.Sheets
    If (ws.Name <> "MyStoreInfo") Then
        ws.Visible = False

is allready hidden, it doesnt work...

 

[sdoppke]

It give an error and sais ws.Visible=-1

 

[tweedle]

You're getting there; I don't see any issue with the code in post #3.
You can still set the visible property to false, even if it is already false.
(One may not .Select a hidden sheet)

 

[Sektor]

How do you define wb variable?

 

[Weaver]

This hides them all (apart from a dummy sheet created for the purpose) then reveals anything in the array

Sub hideallbut()
    Dim ws, ma, ts
    ma = Array("sheet2", "sheet3")
    Application.ScreenUpdating = False
    Set ts = Sheets.Add
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> ts.Name Then ws.Visible = False
    Next
    For Each a In ma
        Sheets(a).Visible = True
    Next
    Application.DisplayAlerts = False
    ts.Delete
    Application.DisplayAlerts = True
End Sub