VBA Tabs Unhide to Hide ?

[Pinaceous]

Hi All;

I am using Excel 2013 and I can of course record a macro to unhide all tabs in a worksheet (& also to do its reverse in a separate macro).

My ? is; is there a written code to expidite this process as opposed to recording one?

Many thanks in advance.

R/

-Pin

 

[Peter_SSs]

I can of course record a macro to unhide all tabs in a worksheet (& also to do its reverse in a separate macro).

Not quite the reverse as a workbook must always have at least one visible worksheet.
Try these though.

To make all visible

Sub Unhide_All()
  Dim ws As Worksheet
  
  For Each ws In Sheets
    ws.Visible = xlSheetVisible
  Next ws
End Sub

To hide all except the left hand sheet

Sub HideAll_ExceptOne_v1()
  Dim i As Long
  
  Sheets(1).Visible = xlSheetVisible
  For i = 2 To Sheets.Count
    Sheets(i).Visible = xlSheetHidden
  Next i
End Sub

To hide all except one with a specific name

Sub HideAll_ExceptOne_v2()
  Dim ws As Worksheet
  
  Const KeepVisible As String = "Sheet2"
  
  Sheets(KeepVisible).Visible = xlSheetVisible
  For Each ws In Sheets
    ws.Visible = ws.Name = KeepVisible
  Next ws
End Sub

 

[My Aswer Is This]

Peter:

I don't understand how this part of your script works. I see it works but don't understand what "xl" means.

ws.Visible = xlSheetVisible

Trying to read and understand code.

 

[Peter_SSs]

Peter:

I don't understand how this part of your script works. I see it works but don't understand what "xl" means.

ws.Visible = xlSheetVisible

Trying to read and understand code.

The 'xl' is just part of the syntax. If you look at the vba Help you will find there are three states of worksheet visibility:

xlSheetVisible, xlSheetHidden, xlSheetVeryHidden

These can be substituted with, respectively, the following values -1, 0, 2

On this occasion I chose to use the full name as it is more descriptive of what it does.

 

[My Aswer Is This]

Ok. Understand. Thanks

 

[Pinaceous]

Hi Peter,

You are right, one sheet has to be visible, sorry on my verbiage. Let me give it a go.

Thanks again
-Pin