Unhiding multiple tabs in Excel 2003

[Maceod1825]

Daily I have five Excel files that I need to open, unhide 15 tabs, update them in Essbase, and then hide them again. I can hide the tabs in each file at the same time but can't figure out how to unhide all of the 15 tabs at the same time. Can anyone give me some assistance in this matter. Thanks in advance for your help.

 

[ADAMC]

How are you hiding all the tabs at the same time?

Probably use some VBA to unhide all the worksheets if thats possible?

something like:

Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible
Next

Application.ScreenUpdating = True

End Sub

in the workbook open event might be what you need...?

****Edit******
The code above would unhide all worksheets I just noticed this may not be what you need but please post back with details sure we can come up with something

 

[Maceod1825]

I hide them all at the same time. I just highlight all of the tabs I need to hide with the Ctrl key then Format, Sheet, Hide. This function isn't avaliable when I want to unhide all of the tabs. I was hoping for some sort of process that will unhide them. I will give the VB code a try. Thanks for your help.

 

[ADAMC]

No problem

Private Sub Workbook_Open()

Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible
Next

Application.ScreenUpdating = True

End Sub

Paste the code above in the this workbook module of the Visual Basic Editor
Save your workbook
close it
Re -open
the above event will be triggered and all worksheets will be visible.
You could prob do this backwards on the workbook before close event too so they are all hidden again.........
Obv this unhides ALL worksheets, if this isnt what you need or you have any questions please just post back.

Hope this helps

 

[Maceod1825]

I copied the above VB code into my spreadsheet, saved, then closed. When I reopened the file nothing happened. Is the code supposed to automatically unhide the tabs?

 

[ADAMC]

Yes it should.......

Private Sub Workbook_Open()

Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible
Next

Application.ScreenUpdating = True

End Sub

Make sure its all the code above...if you used the first post i forgot to include the workbook open bit........

Make sure the code is pasted into the 'Thisworkbook' module not a sheet module.

With excel open...Press Alt + F11 to open VBE
In the left hand side double click on the 'thisworkbook'module
paste the code in there.
save
close
Open (See what happens)

 

[Maceod1825]

That worked percectly. Thank you so much for your help.

 

[ADAMC]

very welcome thanks for the feedback:

As a time saver you could also do the same to make them all hidden when the workbook is closed.

You cant hide all worksheets in a workbook one must be left visible. the code below copied and pasted as is in the same place as the previous code will hide ALL worksheets EXCEPT the active worksheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
If sh.Name <> ActiveSheet.Name Then sh.Visible = xlSheetHidden
Next

Application.ScreenUpdating = True


End Sub

As is this code will still prompt to save changes before it actually closes if you want to bypass this have the sheets hidden/ workbook saved then auto close

add a line to save:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
If sh.Name <> ActiveSheet.Name Then sh.Visible = xlSheetHidden
Next
me.save
Application.ScreenUpdating = True


End Sub

 

[Maceod1825]

I added the VB code and I am all hooked up now. thank you very much for your help.