Dave,
I tweaked it a bit to show the last two sheets, which are setup and
instructions pages. Just added two lines at the end of your code to always
make those sheets visible.
Two things:
1. Can I insert a validation code to force the entry of the numbers 1-50?
2. On the setup page, I have configuration for 50 technicians for each day
of the week. Monday, 50 - Tuesday, 50, etc. Is it possible to also hide the
ROWS on that page (named Setup) that correspond to the same number of techs
code I am using for the sheets. Example: I enter 19 and 19 sheets appear, but
then I only want 19 tech rows for each day of the week to appear as well. I
can put a number in column A for each teck, for each day, 1-50 on Monday,
1-50 on Tue, etc.
What do you think?

"Dave Peterson" wrote:

Create a worksheet named Index and move it to the leftmost position.

Then drop a command button from the Forms toolbar onto that Index worksheet.

Assign it this macro:

Option Explicit
Sub auto_open()

Dim wCtr As Long
Dim HowMany As Long

Application.ScreenUpdating = False

Worksheets("index").Visible = xlSheetVisible

For wCtr = 1 To Worksheets.Count
If Worksheets(wCtr).Name = Worksheets("index").Name Then
'skip it
Else
Worksheets(wCtr).Visible = xlSheetHidden
End If
Next wCtr

HowMany = CLng(Application.InputBox(Prompt:="how many to show?", Type:=1))

If HowMany < 1 Then
'do nothing
Else
If HowMany Worksheets.Count - 1 Then
HowMany = Worksheets.Count - 1
End If

For wCtr = 2 To HowMany + 1
Worksheets(wCtr).Visible = xlSheetVisible
Next wCtr
End If

Application.ScreenUpdating = True

End Sub

By naming it auto_open, it actually runs when the workbook opens. And hides all
the other worksheets.

But it still can be run via the button.


David wrote:

I have a worksheet that has data for up to 50 technicians. Each location gets
this workbook and may have from 3-50 technicians. I'd like to create a
macro/formula that lets each location enter the number of technicians, and
then will hide the unneeded worksheets.
The workbook has a summary page, (worksheet one), fifty individual
technician pages (worksheets 2-51) and a setup page (worksheet 52). Data is
entered on each technicians page.
What I would like is for the location to entry a number in a cell or from a
macro prompt that asks how many locations they have. Based on that response,
say 10, would leave the first 10 technician pages, but hide the other 40.
Any suggestions? Thanks!

--

Dave Peterson