I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??


--
Thanks

Larry

This works for hidden sheets (not veryHidden)

Sub DelHidden()

Dim sh As Worksheet
For Each sh In Worksheets
With Worksheets(sh.Name)
If Not .Visible = xlSheetVisible Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
End With
Next sh

End Sub


"Larry Fitch" wrote in message
...
I am trying to cre4ate a macro that checks the value of a number of sheets
to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can
some
one tell me what I am doing wrong ??


--
Thanks

Larry

Sub Test1()
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook

Set myWB = ThisWorkbook

If myWS.Name = "Pay Inflation - Biometrics" Or _
myWS.Name = "Statistics" Or _
myWS.Name = "Direct Cost Savings Breakdown" Or _
myWS.Name = "OT Reduction" Or _
myWS.Name = "Nurse OT Reduction" Or _
myWS.Name = "Premium Labor Utilization" Or _
myWS.Name = "Pay inflation - Timestamp" Or _
myWS.Name = "Calculation Error" Or _
myWS.Name = "Leave Inflation" Or _
myWS.Name = "Absenteeism" Or _
myWS.Name = "Walk Time Reductions" Or _
myWS.Name = "Paper Costs" Or _
myWS.Name = "Direct Cost Savings" Or _
myWS.Name = "Financial Analysis" Or _
myWS.Name = "Project Timeline Savings " Or _
myWS.Name = "Total Cost of Ownership" Then
With myWS
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
'You actually can have xlSheetHidden or
'xlSheetVeryHidden here.

End If
End With
End Sub


Try this. Keep in mind that you have to have one worksheet visible in the
workbook.
--
HTH,

Barb Reinhardt



"Larry Fitch" wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??


--
Thanks

Larry

Hi Barb -

Thanks very much for the reply.. When I use this code I get an error "Block
If without End If"
--
Thanks

Larry


"Barb Reinhardt" wrote:

Sub Test1()
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook

Set myWB = ThisWorkbook

If myWS.Name = "Pay Inflation - Biometrics" Or _
myWS.Name = "Statistics" Or _
myWS.Name = "Direct Cost Savings Breakdown" Or _
myWS.Name = "OT Reduction" Or _
myWS.Name = "Nurse OT Reduction" Or _
myWS.Name = "Premium Labor Utilization" Or _
myWS.Name = "Pay inflation - Timestamp" Or _
myWS.Name = "Calculation Error" Or _
myWS.Name = "Leave Inflation" Or _
myWS.Name = "Absenteeism" Or _
myWS.Name = "Walk Time Reductions" Or _
myWS.Name = "Paper Costs" Or _
myWS.Name = "Direct Cost Savings" Or _
myWS.Name = "Financial Analysis" Or _
myWS.Name = "Project Timeline Savings " Or _
myWS.Name = "Total Cost of Ownership" Then
With myWS
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
'You actually can have xlSheetHidden or
'xlSheetVeryHidden here.

End If
End With
End Sub


Try this. Keep in mind that you have to have one worksheet visible in the
workbook.
--
HTH,

Barb Reinhardt



"Larry Fitch" wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??


--
Thanks

Larry

Looks like it's missing the closing End If that should be right before End Sub.


"Larry Fitch" wrote:


Hi Barb -

Thanks very much for the reply.. When I use this code I get an error "Block
If without End If"
--
Thanks

Larry


"Barb Reinhardt" wrote:

Sub Test1()
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook

Set myWB = ThisWorkbook

If myWS.Name = "Pay Inflation - Biometrics" Or _
myWS.Name = "Statistics" Or _
myWS.Name = "Direct Cost Savings Breakdown" Or _
myWS.Name = "OT Reduction" Or _
myWS.Name = "Nurse OT Reduction" Or _
myWS.Name = "Premium Labor Utilization" Or _
myWS.Name = "Pay inflation - Timestamp" Or _
myWS.Name = "Calculation Error" Or _
myWS.Name = "Leave Inflation" Or _
myWS.Name = "Absenteeism" Or _
myWS.Name = "Walk Time Reductions" Or _
myWS.Name = "Paper Costs" Or _
myWS.Name = "Direct Cost Savings" Or _
myWS.Name = "Financial Analysis" Or _
myWS.Name = "Project Timeline Savings " Or _
myWS.Name = "Total Cost of Ownership" Then
With myWS
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
'You actually can have xlSheetHidden or
'xlSheetVeryHidden here.

End If
End With
End Sub


Try this. Keep in mind that you have to have one worksheet visible in the
workbook.
--
HTH,

Barb Reinhardt



"Larry Fitch" wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??


--
Thanks

Larry

First, is this a typo:

Sheets ("Project Timeline Savings ")

Is there really an extra space character after "savings"????

I'm assuming that it's a typo in the post.

And you're asking to leave those worksheets alone--don't change the visibility
of any of them. Keep them hidden if they're hidden or keep them visible if
they're visible, right?

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'do nothing
Case Else
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

Larry Fitch wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??

--
Thanks

Larry

--

Dave Peterson

Hi David -

so this works - but it is also is opening the rest of the sheets in the
workbook as well.. I only want the sheets listed to be affected..
--
Thanks

Larry


"Dave Peterson" wrote:

First, is this a typo:

Sheets ("Project Timeline Savings ")

Is there really an extra space character after "savings"????

I'm assuming that it's a typo in the post.

And you're asking to leave those worksheets alone--don't change the visibility
of any of them. Keep them hidden if they're hidden or keep them visible if
they're visible, right?

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'do nothing
Case Else
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

Larry Fitch wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??

--
Thanks

Larry

--

Dave Peterson
.

Maybe...

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

============
If this doesn't do what you want, maybe you could explain it one more time in
plain words.

Larry Fitch wrote:

Hi David -

so this works - but it is also is opening the rest of the sheets in the
workbook as well.. I only want the sheets listed to be affected..
--
Thanks

Larry

"Dave Peterson" wrote:

First, is this a typo:

Sheets ("Project Timeline Savings ")

Is there really an extra space character after "savings"????

I'm assuming that it's a typo in the post.

And you're asking to leave those worksheets alone--don't change the visibility
of any of them. Keep them hidden if they're hidden or keep them visible if
they're visible, right?

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'do nothing
Case Else
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

Larry Fitch wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??

--
Thanks

Larry

--

Dave Peterson
.


--

Dave Peterson

Woo Hoo !!

That worked David...

Thanks for all the help..
--
Thanks

Larry


"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

============
If this doesn't do what you want, maybe you could explain it one more time in
plain words.

Larry Fitch wrote:

Hi David -

so this works - but it is also is opening the rest of the sheets in the
workbook as well.. I only want the sheets listed to be affected..
--
Thanks

Larry

"Dave Peterson" wrote:

First, is this a typo:

Sheets ("Project Timeline Savings ")

Is there really an extra space character after "savings"????

I'm assuming that it's a typo in the post.

And you're asking to leave those worksheets alone--don't change the visibility
of any of them. Keep them hidden if they're hidden or keep them visible if
they're visible, right?

Option Explicit
Sub Test1()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("Pay Inflation - Biometrics"), _
LCase("Statistics"), _
LCase("Direct Cost Savings Breakdown"), _
LCase("OT Reduction"), _
LCase("Nurse OT Reduction"), _
LCase("Premium Labor Utilization"), _
LCase("Pay inflation - Timestamp"), _
LCase("Calculation Error"), _
LCase("Leave Inflation"), _
LCase("Absenteeism"), _
LCase("Walk Time Reductions"), _
LCase("Paper Costs"), _
LCase("Direct Cost Savings"), _
LCase("Financial Analysis"), _
LCase("Project Timeline Savings"), _
LCase("Total Cost of Ownership")
'do nothing
Case Else
'if the sheet is already visible, then this won't hurt
wks.Visible = xlSheetVisible
End Select
Next wks
End Sub

Larry Fitch wrote:

I am trying to cre4ate a macro that checks the value of a number of sheets to
determine if they are already visible and if so it will not close them -

With Sheets("Pay Inflation - Biometrics")
Sheets ("Statistics")
Sheets ("Direct Cost Savings Breakdown")
Sheets ("OT Reduction")
Sheets ("Nurse OT Reduction")
Sheets ("Premium Labor Utilization")
Sheets ("Pay inflation - Timestamp")
Sheets ("Calculation Error")
Sheets ("Leave Inflation")
Sheets ("Absenteeism")
Sheets ("Walk Time Reductions")
Sheets ("Paper Costs")
Sheets ("Direct Cost Savings")
Sheets ("Financial Analysis")
Sheets ("Project Timeline Savings ")
Sheets ("Total Cost of Ownership")
If .Visible < xlSheetVisible Then
.Visible = Not .Visible
End If
End With

When I run the macro I get the "invalid use of property" message.. Can some
one tell me what I am doing wrong ??

--
Thanks

Larry

--

Dave Peterson
.


--

Dave Peterson
.