Tabs within Tabs

**Palmetto** · 12-22-2009, 01:54 PM

Not sure what you are after, but if you're looking for a "drill down" type of effect, perhaps you should take a look at Pivot Tables.

In short, rows and columns can be hidden in worksheets, but there are no other "layers" of objects to expand or collapse. There are, of course, user forms and other objects, but they not intended for general data storage.

As Stephen suggested, clarify what you are doing and to help us understand, post a sample workbook that shows what you are trying to achieve.

**nofzinger** · 12-22-2009, 02:44 PM

It seems that what I want is not possible, which is what I thought.

I just want an extra layer of classification. Suppose I have a folder on my desktop called USA (e.g. a workbook). In that folder, I have 50 states (tabs or worksheets). In the 50 states, I have all the cities (?). That's the "layering" I'm trying to describe.

Basically, I have 50 distinct clients, each with 2-20 accounts. Instead of just having John1, John2, Betsy1, Betsy2, Betsy3, etc., I would like John and Betsy...and when I click on John, I get Account 1, Account 2....

It's purely an organizational and aesthetic thing. I'm not having difficulty with data or anything, I just want to look at my tabs have them be more organized. I'm surprised Mircosoft hasn't thought of something like this, but maybe no one else cares.

Thanks.

**shg** · 12-22-2009, 02:52 PM

How about arranging the data in a single table, with columns for State, City, Client, Account, ... and using AutoFilter?

**nofzinger** · 12-22-2009, 03:13 PM

...I'm not sure that's what I'm looking for here.

I just want to be able to group tabs. Like, when you hide columns, you can have that (+) that opens them...that's what I want for tabs. I want to be able to have 4 tabs that pertain to a specific family, have one tab represent the family, and when you click it, all the tabs pertaining to that family will show. So, instead of having Mary1, Mary2 and John1, John2, and John3 as seperate tabs....I have Mary and John. When you click the Mary tab, Mary1 and Mary2 show....but John is still John, not John1, John2, John3....It's purely organizational and aesthetic (I apologize for repeating myself). I just want to be able to look at my tabs, have 1/5 the tabs to look at, and if I'm working on Mary, I'll just click on Mary and out pops Mary1 and Mary2.

I apologize for not making myself clear....I'm just waiting for someone to say "No, that cannot be done..." and I'll put up a big [SOLVED].

Thanks.

**RxMiller** · 12-22-2009, 03:28 PM

Perhaps major tabs showing and the minor tabs hidden.
Then with in each major tab, add code that checks to determine what major tab last had the focus - and use that association to dynicaclly unhide the minor tabs

Example:
Major tabs: Name of a state
Minor tabs: name of sales offices

**Palmetto** · 12-22-2009, 03:55 PM

I just want to be able to group tabs. Like, when you hide columns, you can have that (+) that opens them...that's what I want for tabs.

First - each worksheet must have a unique name. So name them John1, John2, etc, but hide the sheet tabs if you don't want to see the tab names.

If you want to show only all of John or Mary's worksheets, while hiding all sheets not in their group, you can achieve this using a drop down data validation list of names and using a bit of VBA to show related sheets while hiding others. See attached. This is as close as it gets.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet, strName As String
    
    Application.ScreenUpdating = False
    
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        strName = Target.Value
        
        On Error Resume Next
        
        For Each ws In Worksheets
            If ws.Name Like strName & "*" Or ws.Name Like "Sheet1" Then
                ws.Visible = xlSheetVisible
            Else
                ws.Visible = xlSheetHidden
            End If
        Next ws
    End If
    Application.ScreenUpdating = True

End Sub

**suhi** · 01-23-2011, 01:37 PM

Deleted post

**chriska416** · 12-09-2014, 09:16 PM

I see I'm a little late to the party, but I'm hoping someone is still monitoring this. I would like to use the above code, but my situation is slightly different. I have a list of work locations, and employees at those locations. I'd love to have tabs (employees), within a tab (location), but I know that isn't possible. I want to sort by Location, but each tab need to be labeled with the employee name. The above code utilizes the tab names, however that won't exactly work for me. I was wondering if there was a way to sort/filter by a cell within the tab. I can make a specific cell (ie: G1) the location name on every sheet. Would this be possible? and if so, how?

Please and thank you, Chris