Syntax to match sheet by codename
try sht.name=shta.name
"ker_01" wrote in message
...
I'm attempting to show/hide sheets via code, so I can see all the sheets
when
updating a workbook, then hide the source sheets so my users just see the
output sheets.
unhiding every sheet is easy;
Sub ShowAllSheets()
Dim sht As Worksheet
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End Sub
But when trying to re-hide, I need to exclude the user sheets. I know how
to
do this by setting up an array of every sheet to be hidden, but that is a
lot
of extra code. Here is my attempt to hide everything except three user
sheets
(I've tried several variations) with no luck. I keep getting a 438 runtime
error, "object does not support this property or method"
Sub HideAllSheets()
Dim sht As Worksheet
Dim shta As Worksheet
Dim shtb As Worksheet
Dim shtc As Worksheet
Set shta = Sheet2
Set shtb = Sheet4
Set shtc = Sheet17
For Each sht In Excel.ActiveWorkbook.Worksheets
If (sht = shta) Or (sht = shtb) Or (sht = shtc) Then '<--- this is
where it fails
'do nothing
Else
sht.Visible = xlSheetHidden
End If
Next
End Sub
|