too many sheets

Question

I have a spread sheet program that has over 100 sheets across bottom. Can they be grouped into folders? They all share a common pricing sheet, which is located at the end. This makes it very time consuming clicking to the end every time I write a new sheet to pull values or locate a specific sheet.

Answer 1

I have a spread sheet program that has over 100 sheets across bottom. Can they be grouped into folders? They all share a common pricing sheet, which is located at the end. This makes it very time consuming clicking to the end every time I write a new sheet to pull values or locate a specific sheet.  

Try this code from Bob Phillips to get a sheet menu.

Sub BrowseSheets()

Const nPerColumn  As Long = 38          'number of items per column

Const nWidth As Long = 13                'width of each letter

Const nHeight As Long = 18              'height of each row

Const sID As String = "___SheetGoto"    'name of dialog sheet

Const kCaption As String = " Select sheet to goto"

                                        'dialog caption

Dim i As Long

Dim TopPos As Long

Dim iBooks As Long

Dim cCols As Long

Dim cLetters As Long

Dim cMaxLetters As Long

Dim cLeft As Long

Dim thisDlg As DialogSheet

Dim CurrentSheet As Worksheet

Dim cb As OptionButton

    Application.ScreenUpdating = False

    If ActiveWorkbook.ProtectStructure Then

        MsgBox "Workbook is protected.", vbCritical

        Exit Sub

    End If

    On Error Resume Next

        Application.DisplayAlerts = False

        ActiveWorkbook.DialogSheets(sID).Delete

        Application.DisplayAlerts = True

    On Error GoTo 0

    Set CurrentSheet = ActiveSheet

    Set thisDlg = ActiveWorkbook.DialogSheets.Add

    With thisDlg

        .Name = sID

        .Visible = xlSheetHidden

        'sets variables for positioning on dialog

        iBooks = 0

        cCols = 0

        cMaxLetters = 0

        cLeft = 78

        TopPos = 40

        For i = 1 To ActiveWorkbook.Worksheets.Count

            If i Mod nPerColumn = 1 Then

                cCols = cCols + 1

                TopPos = 40

                cLeft = cLeft + (cMaxLetters * nWidth)

                cMaxLetters = 0

            End If

            Set CurrentSheet = ActiveWorkbook.Worksheets(i)

            cLetters = Len(CurrentSheet.Name)

            If cLetters > cMaxLetters Then

                cMaxLetters = cLetters

            End If

            iBooks = iBooks + 1

            .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5

            .OptionButtons(iBooks).Text = _

                ActiveWorkbook.Worksheets(iBooks).Name

            TopPos = TopPos + 13

        Next i

        .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

        CurrentSheet.Activate

        With .DialogFrame

            .Height = Application.Max(68, _

                Application.Min(iBooks, nPerColumn) * nHeight + 10)

            .Width = cLeft + (cMaxLetters * nWidth) + 24

            .Caption = kCaption

        End With

        .Buttons("Button 2").BringToFront

        .Buttons("Button 3").BringToFront

        Application.ScreenUpdating = True

        If .Show Then

            For Each cb In thisDlg.OptionButtons

                If cb.Value = xlOn Then

                    ActiveWorkbook.Worksheets(cb.Caption).Select

                    Exit For

                End If

            Next cb

        Else

            MsgBox "Nothing selected"

        End If

        Application.DisplayAlerts = False

        .Delete

    End With

End Sub

Gord

Answer 2

You have a couple of options that might help, with a minimum amount of VBA coding.

You could have a drop-down list of all the tab names on a master sheet, and when you select a name, automatically activate that sheet

You could list the sheets in groups, one sheet name per cell, so that when you click a cell, that sheet is activated

You could have all the sheets hidden, and only unhide the relevant sheet(s) or group(s) based on a selection

Although the code would vary by which version of excel you are using (so less transferrable across machines, and harder to program in the first place) you could add one or more drop-downs to one of the ribbons (I think the developer ribbon by default) to allow you to select which sheet to activate

If you are looking for a meta-grouping of worksheet tabs, I'm not aware of any built-in Excel functionality for that. I guess at best (moderate coding) you could have worksheets with your group names, and use the worksheet_selection event (in VBA coding) to then unhide the sheets in that group for selection- but I'm not sure what you would want to use to trigger the re-hide of those sheets to bring you back to your general usage state.