Hide/show sheets by first part of sheet name

[Brentus]

I use Excel 2000 and have a workbook with 100 or so sheets. Since
navigating these sheets is cumbersome, I am looking at a way to
automate the hiding and showing of a group of sheets. What I would
like to see Microsoft do is provide functionality to collapse and
expand a group of sheets, but until that happens, perhaps a macro is
the best way to accomplish the task.

I have 10-30 sheets of individuals in the same region and each sheet
begins with the region abbreviation and then last name, first intial
(NE--Wilson, A.; SE--Davis, D.; etc.). Since I have little VBA
experience, I do not know how to create a macro that will select all
sheets that start with...

Also, if anyone has suggestions of other ways they manage navigation
in large workbooks, please advise.

Thanks,
Brent

 

[Ron de Bruin]

Hi Brentus

Try something like this
This will only show the sheets with a name that starts with "ne" or "NE"

Sub test()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Left(LCase(sh.Name), 2) = "ne" Then
sh.Visible = xlSheetVisible
Else
sh.Visible = xlSheetHidden
End If
Next
End Sub

 

[JE McGimpsey]

Just a variant:

Sub test()
Dim sh As Worksheet
On Error Resume Next
For Each sh in ThisWorkbook.Worksheets
With sh
.Visible = Left(LCase(.Name), 2) = "ne"
End With
Next sh
On Error GoTo 0
End Sub

The "On Error Resume Next" is just in case there are no sheets starting
with "ne" - which would cause the last worksheet to throw a run-time
error since a workbook has to have at least one visible sheet.