-1

I have an excel 2007 file with many sheets and I need to hide or show all of them (except one).

It is easy to hide all the sheet, but how to show all the 200 sheets togheter?

I know I can write a macro to show the sheets but to ride this way I have to extract all the sheets name to go on.

1 Answer 1

2

Not sure why you'd have to extract all the sheet names.

Hiding sheets

AFAIK there are two ways to do it.

Select all the sheets that you want to hide, and then right-click and select "hide".
Or, On the Home tab, in the Cells group, click Format, and under Visibility, click Hide & Unhide, and then click Hide Sheet.

enter image description here

The other way of doing it is by looping the sheets with a simple macro, hiding all sheets apart from the currently selected one:

Sub hideSheets()
Dim wS As Worksheet, Current As String
Current = ActiveSheet.Name
For Each wS In Worksheets
    If Not wS.Name = Current Then
        wS.Visible = False
    End If
Next
End Sub

Showing sheets

To show all the sheets again, the code is even simpler.

Sub showSheets()
Dim wS As Worksheet
For Each wS In Worksheets
        wS.Visible = True
Next
End Sub

Extra

If you want to target a specific sheet, that isn't the currently active one (to hide all but, or show all but), simply change the Current = ActiveSheet.Name to Current = InputBox("Enter Sheet Name") and you get to name the sheet in an inputbox instead.

2
  • I edit the question because the problem is on showing all the sheets
    – Ale
    Feb 22, 2017 at 8:32
  • @Ale Added simple macro to show all the sheets. Feb 22, 2017 at 8:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .