CAN I GROUP TABS UNDER A "MASTERTAB" IN EXCEL WORKBOOK
 

CAN I GROUP TABS UNDER A "MASTERTAB" IN EXCEL WORKBOOK....HAVE MANY TABS AND
WISH TO HAVE THEM IN SEPERATE CATEGORIES...THANKS

CAN I GROUP TABS UNDER A "MASTERTAB" IN EXCEL WORKBOOK
 

Hi KJR,

Add 'Master' sheets and name them Master1, Master2 ...

In the code module behind the new Master1 sheet paste code like:

'=============
Private Sub Worksheet_Activate()
Dim sh As Worksheet 'Object
Dim arr As Variant

arr = Array("Sheet1", "Sheet2", "Sheet3") ' <<=== CHANGE

On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each sh In ThisWorkbook.Sheets
If Not sh.Name Like "Master*" Then
sh.Visible = xlSheetHidden
End If
Next sh

For Each sh In Sheets(arr)
sh.Visible = xlSheetVisible
Next sh

XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

'--------------------------

Private Sub Worksheet_Deactivate()
Dim sh As Worksheet
Dim arr As Variant

arr = Array("Sheet1", "Sheet2", "Sheet3") ' <<=== CHANGE

On Error GoTo XIT
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each sh In Sheets(arr)
sh.Visible = xlSheetVisible
Next sh

XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'<<=============

In each of the other Master sheet code modules paste similar code, changing
the sheet names listed in the statement lines:

arr = Array("Sheet1", "Sheet2", "Sheet3") ' <<=== CHANGE

Note that this line needs to be changed in each of the two subs.

The reult of this arrangement would be that the master sheet tabs will
always be available but on;y the sheets associated with the selected master
sheet will be visible.

---
Regards,
Norman


"KJR" wrote in message
...
CAN I GROUP TABS UNDER A "MASTERTAB" IN EXCEL WORKBOOK....HAVE MANY TABS
AND
WISH TO HAVE THEM IN SEPERATE CATEGORIES...THANKS

CAN I GROUP TABS UNDER A "MASTERTAB" IN EXCEL WORKBOOK
 

Hi KJR,

I should add that this is worksheet event code and should be pasted into
the master sheets's code module (not a standard module and not the
workbook's ThisWorkbook module):

Right-click the Master sheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman