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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brentus" wrote in message om...
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

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.


In article ,
"Ron de Bruin" wrote:

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