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!

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

It's perfect! Thanks!

"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

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

The easy one first.

#1. Why bother? The code stops if you don't enter at least 1 and if the user
enters 999, then the code stops after it shows the last worksheet. I wouldn't
change it--it would just mean one more thing to modify if/when you add more
sheets.

And by doing what the user wants (if they type 999, you show all the sheets), it
makes it easier for them.

The more difficult one.
#2. I'm not sure I quite understand.

But maybe you could apply data|filter|autofilter to that range and show the
values that are less than the number entered.

I applied the filter manually (so I know it's there) and then filtered by that
first column. You should be able to add the important portion of this code into
your existing macro:

Option Explicit
Sub part2()

Dim HowMany As Long

HowMany = 4

'add this to the bottom of your code
With Worksheets("setup")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
MsgBox "Contact the other Dave to apply the filter!"
Exit Sub
End If

.AutoFilter.Range.AutoFilter field:=1, Criteria1:="<=" & HowMany
End With

End Sub

If you have trouble, post your existing code in your reply.

David wrote:

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


--

Dave Peterson

Again, you have managed to give me exactly what I needed! It works flawlessly!!
For those rows that I did not want hidden under any circumstance, I assigned
the number 0, so they always appear. Thanks again!!

PS - I just put a note in the Msg Box to enter the correct range. I'm hiding
the index sheet and have the button on the Setup sheet. When the macro
starts, it unhides the index page, but then hides it again at the end. It was
giving me an error when anything outside the range was entered...but I got it
worked out.

Thanks much again!!!

"Dave Peterson" wrote:

The easy one first.

#1. Why bother? The code stops if you don't enter at least 1 and if the user
enters 999, then the code stops after it shows the last worksheet. I wouldn't
change it--it would just mean one more thing to modify if/when you add more
sheets.

And by doing what the user wants (if they type 999, you show all the sheets), it
makes it easier for them.

The more difficult one.
#2. I'm not sure I quite understand.

But maybe you could apply data|filter|autofilter to that range and show the
values that are less than the number entered.

I applied the filter manually (so I know it's there) and then filtered by that
first column. You should be able to add the important portion of this code into
your existing macro:

Option Explicit
Sub part2()

Dim HowMany As Long

HowMany = 4

'add this to the bottom of your code
With Worksheets("setup")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
MsgBox "Contact the other Dave to apply the filter!"
Exit Sub
End If

.AutoFilter.Range.AutoFilter field:=1, Criteria1:="<=" & HowMany
End With

End Sub

If you have trouble, post your existing code in your reply.

David wrote:

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


--

Dave Peterson

Glad you got it working...

(We David's have to keep together!)

<vbg

David wrote:

Again, you have managed to give me exactly what I needed! It works flawlessly!!
For those rows that I did not want hidden under any circumstance, I assigned
the number 0, so they always appear. Thanks again!!

PS - I just put a note in the Msg Box to enter the correct range. I'm hiding
the index sheet and have the button on the Setup sheet. When the macro
starts, it unhides the index page, but then hides it again at the end. It was
giving me an error when anything outside the range was entered...but I got it
worked out.

Thanks much again!!!

"Dave Peterson" wrote:

The easy one first.

#1. Why bother? The code stops if you don't enter at least 1 and if the user
enters 999, then the code stops after it shows the last worksheet. I wouldn't
change it--it would just mean one more thing to modify if/when you add more
sheets.

And by doing what the user wants (if they type 999, you show all the sheets), it
makes it easier for them.

The more difficult one.
#2. I'm not sure I quite understand.

But maybe you could apply data|filter|autofilter to that range and show the
values that are less than the number entered.

I applied the filter manually (so I know it's there) and then filtered by that
first column. You should be able to add the important portion of this code into
your existing macro:

Option Explicit
Sub part2()

Dim HowMany As Long

HowMany = 4

'add this to the bottom of your code
With Worksheets("setup")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
MsgBox "Contact the other Dave to apply the filter!"
Exit Sub
End If

.AutoFilter.Range.AutoFilter field:=1, Criteria1:="<=" & HowMany
End With

End Sub

If you have trouble, post your existing code in your reply.

David wrote:

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


--

Dave Peterson


--

Dave Peterson