Unhiding All hidden Worksheets with a Macro/VBA Code

[robertguy]

Hi,

Can anyone help me please ?

Whilst working with an Excel Workbook I often hide all unwanted Worksheets leaving only the ones I need to work on visible.

What I would like to do is have a macro or VBA code to click on a button to unhide all hidden worksheets rather repeating Format-Sheet-Unhide to unhide each worksheet.

Any help would be greatly appreciated (Excel version Excel 2000)


Many thanks

Rob

[bigwheel]

Try this

Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Visible = True
Next mySheet


"robertguy" wrote:

>
> Hi,
>
> Can anyone help me please ?
>
> Whilst working with an Excel Workbook I often hide all unwanted
> Worksheets leaving only the ones I need to work on visible.
>
> What I would like to do is have a macro or VBA code to click on a
> button to unhide all hidden worksheets rather repeating
> Format-Sheet-Unhide to unhide each worksheet.
>
> Any help would be greatly appreciated (Excel version Excel 2000)
>
>
> Many thanks
>
> Rob
>
>
> --
> robertguy
>
> 44 Married two kids
> ------------------------------------------------------------------------
> robertguy's Profile: http://www.excelforum.com/member.php...fo&userid=5963
> View this thread: http://www.excelforum.com/showthread...hreadid=490133
>
>

[robertguy]

It Works

Many thanks to bigwheel !!

Rob

[milindesai]

You can try following to hide, change the sheetName according to your requirement.


Sub UnHideThem()

For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "SheetName" Then sh.Visible = True
Next sh

End Sub


If you want to have a macro created to hide all worksheets, use following code in your macro module.


Sub HideThem()

For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "Access_Control" Then sh.Visible = False
Next sh

End Sub


Do let me know if this helps.

[robertguy]

milindesai

many thanks for yot reply, the 'UnHideThem' macro works as requested but unfortunately the 'HideThem' hides all but one sheet and then reports an error message i.e. Run-time error '1004': Unable to set the visible property of the Worksheet class.


Do you have any ideas how to resolve this error message


Many thanks


Rob