Reputation: 175
My workbook has both chart sheets and normal sheets, therefore I am using Sheets
instead of Worksheets
. However, I don't know what the type of sht should be in the following set of codes. I am sure it cannot be Worksheet
.
' Hide all sheets/charts except Sheet1
Sub Hide_Sheets()
Dim sht As ???
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> Sheet3.Name Then
sht.Visible = False
End If
Next sht
End Sub
Upvotes: 1
Views: 1228
Reputation: 22358
"Charts and Worksheets are two different collections." --> https://stackoverflow.com/a/6804704/138938
If you have both chart sheets and regular worksheets, you can either loop through a collection of objects like this:
Sub Hide_Objects()
Dim wb As Workbook
Dim obj As Object
Set wb = ActiveWorkbook
For Each obj In wb.Sheets
If obj.Name <> "Sheet1" Then
obj.Visible = False
End If
Next obj
End Sub
Or you can loop through both collections like this:
Sub Hide_Sheets_And_Charts()
Dim wb As Workbook
Dim sht As Worksheet
Dim cht As Chart
Set wb = ActiveWorkbook
For Each sht In wb.Worksheets
If sht.Name <> "Sheet1" Then
sht.Visible = False
End If
Next sht
For Each cht In wb.Charts
If cht.Name <> "Sheet1" Then
cht.Visible = False
End If
Next cht
End Sub
Upvotes: 4
Reputation: 5287
Use Variant then step through the code and you'll be able to see what it is. Variant will work without doing anything else.
I recommend using real names for variables to make it easier for you to read your code at some stage in the future.
Sub Hide_Sheets()
Dim sheet_ As Variant
For Each sheet_ In ActiveWorkbook.Sheets
If sheet_.Name <> Sheet3.Name Then
sheet_.Visible = False
End If
Next sheet_
End Sub
Upvotes: 1