hide/show multiple sheets by macro ?

[Simon-ch]

Hi folks,

I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.
I would like to do it like this:

Please Login or Register  to view this content.

' deselect a range or a few defined sheets (don't know if you can define a 'range' with sheets ?)

Please Login or Register  to view this content.

Any suggestions ? The minimum I'd like to work is not having to 'manually' let the macro select all the sheets they have to hide, as they are too many.
Cheers,

Simon

[Simon-ch]

accidental reply.

[shg]

You can't hide all the sheets in a workbook.

Your second example will work, provided not all the sheets are selected. You can do

Please Login or Register  to view this content.

[Simon-ch]

it was supposed to be 1 example :-) Just didn't format it properly:
1. Select all sheets
2. Deselect the ones I want to show
3. Hide all selected sheets

Hope it's clear now... selecting all the sheets by name is just too much work, plus there might be additions.

[royUK]

Here's a userform to list all sheets & hide the ones that are selected

[Andy Pope]

Another version.

Please Login or Register  to view this content.

[Simon-ch]

Thanks for the reply.. but I can't quite figure out how to adapt this user form to my purposes, and it seems to hide the sheets with data instead of the ones that are empty.
(Sheet8,5,2 are shown, the rest hidden)
Is there no easier way to do this ? Maybe another approach:
I would like them to click a button and it just hides all the sheets except the ones that start with the same letters:
[KGM] <-- if you click this userform it shows all sheets with KGM_texttest*


edit: @ Andy:

Thanks, this seems to be a solution, but the code has a problem at 'sht.Select blnReplace' it wants me to debug it.

[Andy Pope]

Please Login or Register  to view this content.

[Simon-ch]

Thanks, but it always returns with a request for debugging because of the same line in the code:
sht.Select blnReplace
I don't really know what it does, so cannot debug it really

[royUK]

What do you mean no data in them, completely empty?

[Andy Pope]

The code simply selects a sheet. The blnReplace variable tells it whether to replace currently selected sheets with latest, in effect ungrouping, or not.

[Simon-ch]

Well I just opened the "Sheetstohide" workbook, clicked on print and it would hide the ones that aren't empty and show the empty ones.
But it isn't really relevant anyway, because it's not the function I am looking for, so sorry for the distraction.
I can't quite seem to get the above code to work, it always stops at
"sht.Select blnReplace" no idea what's wrong with it.
Thanks to you 2 for the help !

Simon

[Simon-ch]

Your sheet works just the way I'd like it to work, how can I now hide the selected sheets ?

When I cope the same code into my workbook it still has the same debugging problem, but what it apparently does is select the first 15 sheets or so, the rest not.

[Andy Pope]

At the end of the routine you would use the code you first posted.

Please Login or Register  to view this content.

[royUK]

The userform only lists sheets with data in, then you select from it to hide.

This example lists all the sheets

[Simon-ch]

Ok, works perfectly in the test sheet but still refuses to work with my own workbook :-(
Could the naming of my sheets be a problem ? Got quite long sheet names with spaces..
It still points to "sht.Select blnReplace"
when I return to the workbook it has selected the first 14 sheets.

[royUK]

Do you have a sheet with KMG in it's name?

[Andy Pope]

Try stepping through the code line by line, use F8 in vbe.

When it errors use this in the immediate window.

Please Login or Register  to view this content.

This will tell you the sheet name that fails. Is it a worksheet/chartsheet?

Failing that can you post the offending workbook? You can strip all the data off the sheets if needs be.

[Andy Pope]

If Roy's suggestion is true then this will handle that case.

Please Login or Register  to view this content.

[Simon-ch]

Ok, I think I figured out why it turned up with an error. I have 2 sheets hidden in the middle and in the end of the workbook. I kind of need the one in the end hidden because I want to make a sum through all the sheets, even if new ones are added, so I named the last sheet "End" with =sum(start:end!g59)
so I don't need to adjust the sum when new sheets are added.
So the code works perfectly without hidden sheets, thanks for your patience, do you have any idea about my new (altough minor) problem ?

Simon

[Andy Pope]

Did the code actually raise an error? If so details of that error would have helped isolate this problem.

Please Login or Register  to view this content.

[Simon-ch]

Yes, sorry, it all works perfectly without hidden sheets really. But I would like to always keep the "End"-sheet hidden, I could just put it in every macro.
But this is the code I have now, works well

Sub KGM()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Visible = True
Next
' I have implemented this because if I used the macro before it would have hidden sheets of course, so unhide all before any macro can be executed.
Dim sht As Object
Dim blnReplace As Boolean
Dim blnAllSheets As Boolean

blnReplace = True
blnAllSheets = True
For Each sht In ActiveWorkbook.Sheets
If UCase(Left(sht.Name, 3)) = "KGM" Then
' leave alone
blnAllSheets = False
Else
sht.Select blnReplace
blnReplace = False
End If
Next

If blnAllSheets Then
MsgBox "Can not hide all sheets", vbExclamation
Else
ActiveWindow.SelectedSheets.Visible = xlSheetHidden
Sheets("Gesamtkosten").Visible = xlSheetVisible
Sheets("Gesamtkosten").Select
End If
'This is because I always want to have "Gesamtkosten" shown, not very elegant again, but easy code for slow learners ;-)
End Sub