When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

John,

Try this

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Worksheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

If it works, it means that you have another type of sheet, such as a chart,
and as you defined Wsh as a worksheet, it mis-matches. You can safely ignore
these, as they won't have a cell on anyway.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Wirt" wrote in message
...
When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

Hi

Don't know. It worked ok for me in excel 2002 unless I tried to hide all
sheets. One sheet must remain unhidden.

Ken

What's in G1 of the worksheet that the code blows up on?

If it's an error, then that could be the cause.

If UCase(Wsh.Range("G1").Text) = "HIDE" Then

Is one way to avoid it.


John Wirt wrote:

When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

--

Dave Peterson

And if you have chartsheets (or other non-worksheets), you'd want to use:

For Each Wsh In ActiveWorkbook.Worksheets



John Wirt wrote:

When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

--

Dave Peterson

As I said in my response :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
And if you have chartsheets (or other non-worksheets), you'd want to use:

For Each Wsh In ActiveWorkbook.Worksheets



John Wirt wrote:

When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

--

Dave Peterson

Yes, you did!

Bob Phillips wrote:

As I said in my response :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
And if you have chartsheets (or other non-worksheets), you'd want to use:

For Each Wsh In ActiveWorkbook.Worksheets



John Wirt wrote:

When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

--

Dave Peterson

--

Dave Peterson