problem with hiding worksheets

[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

 

[JE McGimpsey]

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

 

[crapit]

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.

however, only the chart sheet remain visible from my macro

 

[crapit]

But if I would to hide the chart so how?

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

 

[JE McGimpsey]

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?

 

[JE McGimpsey]

One way would be to explicitly make "main status" or "spare status"
visible before you started looping.

 

[crapit]

OK it works, but the chart sheet is still visible

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?

 

[crapit]

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")

 

[JE McGimpsey]

Right. That's what you said you wanted to do:

The macro suppose to hide all worksheets except for the 2 worksheet
listed below.

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