Issue with Hiding Sheets Depending on Workbook Name

[Kris]

I am having some syntax issue trying to modify this script now. I am
missing something completly obvious can one of you help. (The reasoning

behind this is that when closing the workbook it will save itself twice

each time it saves different sheets will be hidden so that when people
open each workbook only the information they need will be shown.) Here
is my code.

Sub hidebut()
For Each ws In Worksheets
If Active.Workbook = "abefrof.xls" Then
If ws.Name <> "Solution Direct Tracking" Then ws.Visible = True
Next ws


If Active.Workbook = "abetest1.xls" Then
If ws.Name <> "Solution Direct Tracking" Then ws.Visible = False
Next ws


End Sub


Thanks for all the help

[Ardus Petus]

Sub hidebut()
Const sWSVisible = "Solution Direct Tracking"

For Each ws In Workbooks("abefrof.xls").Worksheets
If ws.Name <> sWSVisible Then ws.Visible = True
Next ws

For Each ws In Workbooks("abetest1.xls").Worksheets
If ws.Name <> sWSVisible Then ws.Visible = True
Next ws
End Sub

HTH
--
AP

"Kris" <kris.king@gmail.com> a écrit dans le message de news:
1147350213.732848.17360@y43g2000cwc.googlegroups.com...
>I am having some syntax issue trying to modify this script now. I am
> missing something completly obvious can one of you help. (The reasoning
>
> behind this is that when closing the workbook it will save itself twice
>
> each time it saves different sheets will be hidden so that when people
> open each workbook only the information they need will be shown.) Here
> is my code.
>
> Sub hidebut()
> For Each ws In Worksheets
> If Active.Workbook = "abefrof.xls" Then
> If ws.Name <> "Solution Direct Tracking" Then ws.Visible = True
> Next ws
>
>
> If Active.Workbook = "abetest1.xls" Then
> If ws.Name <> "Solution Direct Tracking" Then ws.Visible = False
> Next ws
>
>
> End Sub
>
>
> Thanks for all the help
>

[Kris]

Thanks for the help. I must be doing something wrong. When I run this
script it gives me an error and highlights this line:

For Each ws In Workbooks("abefrof.xls").Worksheets

I think it is because I don't have this workbook open. What I am trying
to do is run the macro one way if the name is one thing and a different
way if the workbook name is another. Only one is open at a time. Can
you help me figure out what I did wrong?

[Ardus Petus]

Here is your original code, with syntax errors corrected

'---------------
Sub hidebut()
Dim ws As Worksheet

For Each ws In Worksheets
If ActiveWorkbook.Name = "abefrof.xls" Then
If ws.Name <> "Solution Direct Tracking" Then
ws.Visible = True
End If
End If

If ActiveWorkbook.Name = "abetest1.xls" Then
If ws.Name <> "Solution Direct Tracking" Then
ws.Visible = False
End If
End If
Next ws


End Sub
'--------------
"Kris" <kris.king@gmail.com> a écrit dans le message de news:
1147350213.732848.17360@y43g2000cwc.googlegroups.com...
>I am having some syntax issue trying to modify this script now. I am
> missing something completly obvious can one of you help. (The reasoning
>
> behind this is that when closing the workbook it will save itself twice
>
> each time it saves different sheets will be hidden so that when people
> open each workbook only the information they need will be shown.) Here
> is my code.
>
> Sub hidebut()
> For Each ws In Worksheets
> If Active.Workbook = "abefrof.xls" Then
> If ws.Name <> "Solution Direct Tracking" Then ws.Visible = True
> Next ws
>
>
> If Active.Workbook = "abetest1.xls" Then
> If ws.Name <> "Solution Direct Tracking" Then ws.Visible = False
> Next ws
>
>
> End Sub
>
>
> Thanks for all the help
>