Consolidate Sheets

[JoeExcelHelp]

basically I have 100 sheets all with the same format only the data and sheet name differ.. would like to create something like a master sheet with a drop-down that contains the hidden sheet names that when the sheet name is selected the corresponding data populates
What would be the easiest solution.. Hoping Excel has a feature within it.

[Rudi]

What do you mean by: "the corresponding data populates"?
When you select the sheet from the drop down, do you want it to become visible and selected or must some data be populated onto the hidden sheet?

Please clarify.
TX

[JoeExcelHelp]

I dont want data to populate into the hidden sheets
I just want to hide all sheets and pull-up a hidden sheet easily
My initial thought was to create a template with the same format as the hidden sheets with a drop-down. As I select the sheet from the drop-down the hidden sheet data populates
Basically Yes Rudi to your question "When you select the sheet from the drop down, do you want it to become visible and selected"

[Rudi]

It would be more optimal to bring the hidden sheet into view than to repopulate the template sheet with the data from the hidden sheet.
I can start to work on an example workbook for your reference....

[JoeExcelHelp]

Thanks Rudi

[Rudi]

Here is a small sample.
To view the code, right click on the Navigator sheet tab and choose View Code...
Modify as needed...

It's untested, but you should be able to move all your sheets into this template and the drop down should update and provide navigation...

Navigator.xlsm

[Rudi]

A small improvement on the code is to sort the entries in the combo box in ascending order. With a 100+ sheets, it might be easier to locate a sheet name if the list in the combo box is sorted. You can replace the appropriate macro with this new version:

Code: Select all

Private Sub Worksheet_Activate()
Dim sh As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With Sheets("AllSheets")
        .Columns(1).Delete
        For Each sh In ThisWorkbook.Sheets
            If UCase(sh.Name) <> "ALLSHEETS" And UCase(sh.Name) <> "NAVIGATOR" Then
                .Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = sh.Name
            End If
        Next sh
        .Range("A1").Value = "SheetList"
        'The next line sorts the drop down entries in ascending order
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Header:=xlYes
        Sheet1.cboSheets.ListFillRange = "AllSheets!" & .Range(.Range("A2"), .Range("A2").End(xlDown)).Address
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

And this code can be added to a standard module and run to expose all sheets (if needed).

Code: Select all

Sub ShowAllSheets()
Dim sh As Worksheet
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each sh In ThisWorkbook.Sheets
        If UCase(sh.Name) <> "ALLSHEETS" And UCase(sh.Name) <> "NAVIGATOR" Then sh.Visible = xlSheetVisible
    Next sh
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[JoeExcelHelp]

Thank You Rudi.. will give it a try today