Unhiding Multiple Worksheets

When I used to set the visible property of a worksheet to Xlsheetvisible it shows run time error unable to set visible property of worksheet class. 2 worksheets are hidden in the workbook I tried code on your blog as follows:
Sub UnHideMyGroup()
Sheets("Grade Term 1").Visible = True
Sheets("Grade Term 2").Visible = True
End Sub

but it shows compile error. Please guide me the steps to unhide 2 worksheets named as "Grade Term 1" and "Grade Term 2" in the workbook.

A Molloy:
I assume you want to unhide, say, 5 sheets out of a larger total of hidden sheets?
If so, you need to name them in the macro. eg:
Sub UnHideMyGroup()
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet12").Visible = True
Sheets("Sheet15").Visible = True
End Sub

You can do the same to re-hide them - just copy the macro, save with a new name and change the True statements to False.

The second macro is missing the statement to declare the wsSheet variable (i.e. Dim wsSheet As Worksheet)

Very Helpful and worked perfectly!
Thank you very much!

Please advise on how I could unhide a group of worksheets, not one by one...
Thanks!

I've realised what the problem was - I had the workbook protected! All works perfectly - great macros!

I've tried copying and pasting both of these but when I try and run them I am getting a Run Time Error 1004 - Application-defined or object defined error. Any suggestions?

Thanks

Alex, see my last comment for a response to Mike's post... if you want to hide instead of unhide, change xlSheetHidden to xlSheetVisible, and vice versa...

I too would like to know a respone to Mike Lewis last response as i'm also using this code and I would like to know how you could use this code in reverse. I would like to have a command button to allow users to select which tabs to hide.

@Mike: if you run your code line-by-line you'll quickly see that it's treating the "ElseIf" as a part of the "If wsSheet.Visible" if, NOT the "If Msgres = vbYes" if.

The problem is there are two ways to have an If statement: all on one line (without End If, no Else If or Else) and multi-line (must have End If, can have Else If or Else). So you need to make your second If statement multi-line and add an extra End If:

If Msgres = vbYes Then
wsSheet.Visible = xlSheetVisible
ElseIf Msgres = vbCancel Then
Exit Sub
End If

Hi there
I've copied your code and it works really well but if I only want to unhide a couple of worksheets from a large numgber, it scrolls through the whole list before finishing. so I've modified the code slightly as shown below by changing vbYesNo to vbYesNoCancel and adding a line to exit if vbCancel is pressed. This seems to work ok except it takes a number of clicks on vbCancel before it operates. Is there a way to make the vbCancel button operate the first time it is clicked?

Also, it didn't work until I declared wsSheet As Worksheet.

Thank you

Mike Lewis

Sub UnhideSomeSheets()
Dim sSheetName As String
Dim sMessage As String
Dim Msgres As VbMsgBoxResult
Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Visible = xlSheetHidden Then
sSheetName = wsSheet.Name
sMessage = "Unhide the following sheet?" _
& vbNewLine & sSheetName
Msgres = MsgBox(sMessage, vbYesNoCancel)
If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible
ElseIf Msgres = vbCancel Then Exit Sub
End If

Next wsSheet

End Sub