Displaying worksheets
 

I have a workbook with 97 worksheets. I would like the user, using
userform, to decide which sheets they would like to look at. Eac
worksheet has an 8 letter name that describes (to the user and myself
the content of the worksheet.

I have successfully created a macro that allows 4 display options
however I am having trouble breaking it down further.

For example, lets say I have new & used car lots in Washington
Oregon. ( I have a few more variables than this)

I can separate the displays so that the User can look at only new ca
lots or used car lots. But if they want to limit the display further
for example, to display Used lots in Oregon I get a run time erro
#1004 "Select Method of Worksheet Class failure"

I have used the code below successfully before but now I get this erro
when the macro arrives at Activesheet.Next.Select is it because some o
the sheets are hidden?

Sheets("Control").Select
ActiveSheet.Next.Select
For i = 1 To (Sheets.Count - 1)

Jam = ActiveSheet.Name
Jelly = Mid(Jam, 1, 2)
Branch = Mid(BR_Optioncode, j, 2)
If Jelly = Branch Then
Sheets(Jam).Visible = True
ElseIf Jelly < Branch Then
Sheets(Jam).Visible = False
End If
ActiveSheet.Next.Select
On Error Resume Next

Next i

Thanks for your help

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Displaying worksheets
 

Sheets("Control").Select
For each sh in Sheets
Jam = sh.Name
Jelly = Mid(Jam, 1, 2)
Branch = Mid(BR_Optioncode, j, 2)
If Jelly = Branch Then
Sheets(Jam).Visible = True
Else
Sheets(Jam).Visible = False
End If
Next Sh

--
Regards,
Tom Ogilvy



"rduke0" wrote in message
...

I have a workbook with 97 worksheets. I would like the user, using a
userform, to decide which sheets they would like to look at. Each
worksheet has an 8 letter name that describes (to the user and myself)
the content of the worksheet.

I have successfully created a macro that allows 4 display options;
however I am having trouble breaking it down further.

For example, lets say I have new & used car lots in Washington &
Oregon. ( I have a few more variables than this)

I can separate the displays so that the User can look at only new car
lots or used car lots. But if they want to limit the display further,
for example, to display Used lots in Oregon I get a run time error
#1004 "Select Method of Worksheet Class failure"

I have used the code below successfully before but now I get this error
when the macro arrives at Activesheet.Next.Select is it because some of
the sheets are hidden?

Sheets("Control").Select
ActiveSheet.Next.Select
For i = 1 To (Sheets.Count - 1)

Jam = ActiveSheet.Name
Jelly = Mid(Jam, 1, 2)
Branch = Mid(BR_Optioncode, j, 2)
If Jelly = Branch Then
Sheets(Jam).Visible = True
ElseIf Jelly < Branch Then
Sheets(Jam).Visible = False
End If
ActiveSheet.Next.Select
On Error Resume Next

Next i

Thanks for your help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements