problem with hiding worksheets

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

The macro suppose to hide all worksheets except for the 2 worksheet listed
below. Instead, only 2 chart remain visible! HELP

Sub hide_ws()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
wsSheet.Visible = wsSheet.Name = "main status"
wsSheet.Visible = wsSheet.Name = "spare status"

Next wsSheet

End Sub
 
Three potential problems that I see:

1) the name comparison is case sensitive - are your sheet names in lower
case?

2) since the comparisons are made sequentially, "main status" will first
be made visible (wsSheet.Name = "main status"), then not visible
(wsSheet.Name <> "spare status"), so only "spare status" will remain
unhidden.

3) You can't hide all sheets - if you try to hide the last sheet, you'll
get a run-time error. In this case, since you have chart sheets, it
sounds like that won't be a problem.

Try:

Public Sub hide_ws()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
.Visible = LCase(.Name) = "main status" Or _
LCase(.Name) = "spare status"
End With
Next wsSheet
End Sub
 
From ur reply
2) since the comparisons are made sequentially, "main status" will first
be made visible (wsSheet.Name = "main status"), then not visible
(wsSheet.Name <> "spare status"), so only "spare status" will remain
unhidden.
Click to expand...

however, only the chart sheet remain visible from my macro
 
But if I would to hide the chart so how?
JE McGimpsey said:
Three potential problems that I see:

1) the name comparison is case sensitive - are your sheet names in lower
case?

2) since the comparisons are made sequentially, "main status" will first
be made visible (wsSheet.Name = "main status"), then not visible
(wsSheet.Name <> "spare status"), so only "spare status" will remain
unhidden.

3) You can't hide all sheets - if you try to hide the last sheet, you'll
get a run-time error. In this case, since you have chart sheets, it
sounds like that won't be a problem.

Try:

Public Sub hide_ws()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
.Visible = LCase(.Name) = "main status" Or _
LCase(.Name) = "spare status"
End With
Next wsSheet
End Sub
Click to expand...
 
So I suspect problem #1. Is the sheet "spare status" all lowercase?

If you pasted the code from your workbook into your original post, it
also looks like you have two spaces between "spare" and "status", which
could also cause the problem unless the worksheet name does too.

Have you stepped through your code to see what happens when the "main
status" and "spare status" sheets are processed?
 
One way would be to explicitly make "main status" or "spare status"
visible before you started looping.
 
OK it works, but the chart sheet is still visible
JE McGimpsey said:
So I suspect problem #1. Is the sheet "spare status" all lowercase?

If you pasted the code from your workbook into your original post, it
also looks like you have two spaces between "spare" and "status", which
could also cause the problem unless the worksheet name does too.

Have you stepped through your code to see what happens when the "main
status" and "spare status" sheets are processed?
Click to expand...
 
Regarding range() usage, I had a non-fix range value
where by "lastrow" has been assigned

But how do I make it to function as below??
Sheets("Engine Status").Range("p1:lastrow").Copy
Sheets(shtName).Range("b1")
 
Right. That's what you said you wanted to do:
The macro suppose to hide all worksheets except for the 2 worksheet
listed below.
Click to expand...

If you want to hide chart sheets as well as worksheets, you need to loop
over each sheet in the Sheets collection, e.g.:

Public Sub hide_sheets()
Dim shSheet As Object
For Each shSheet In Sheets
With shSheet
.Visible = LCase(.Name) = "main status" Or _
LCase(.Name) = "spare status"
End With
Next shSheet
End Sub
 
Back
Top