Reorder Hidden Sheets

[white84]

I have inherited a file with 200+ hidden sheets, linked and summarized on a "Summary" tab. Every so often I need to unhide 1 or 2 sheets to review, and then re-hide. My problem is, when I right click to un-hide the list is not in numerical order. Does anyone know how to reorder this list? Thanks in advance!

 

[white84]

I had not run the first macro, but when I just did it worked. I sorted my list and still received the same error as mentioned earlier.

For those interested I found my error. Some of my tabs were numeric names only. When I added a leading alpha character the macro ran as designed.

Thanks again!

 

[white84]

Maybe you would like this solution:

Enter a sheet name into Range("A1") or Range("A2")
Then if you double click on the sheet name the Sheet will toggle from visible to not visible

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  3/6/2019  11:05:43 AM  EST
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
Cancel = True
Dim ans As String
ans = Target.Value
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Sheets(ans).Visible = Not Sheets(ans).Visible = True
End If
Exit Sub
M:
MsgBox "Sheets  " & ans & " Does not Exist"
End Sub

I just applied this Macro and WOW does this help!!!! Thank you!!!

 

[white84]

I continue to expand upon this sheet, and hope someone else has a bright idea for this one. I continue to add tabs/sheets throughout the month, and all relevant data is compiled in a table on a summary tab. The table has permanent references to individual cells (sheet232!G$86$) I add rows to the bottom of the table with each new sheet and do a find/replace within the row (find sheet232! and replace with sheet233!). Thanks to the solutions offered above, I now have all sheet names in column A. I think there is a way to use MID to pull in the sheet name from column A, but I can't get it to work. Anyone have any ideas?

 

[white84]

I keep running into a formatting issue. The data in this file is all copied and pasted as values but contains general, currency, and date formats. Anything that wasn't currency is being converted to Date format. I have over 200 tabs and I have been opening each tab, changing the cells with Date format back to General but I believe the next time I run a macro this work will be undone. I have 3 macros. One to Hide and Unhide the sheets, one to sort the sheets, and one to format labels in a few charts. Could the chart formatting code be leading to this?

Sub FormatLabels()Dim s As Series, y, dl As DataLabel, i%, r As Range
Set r = [j5]
Set s = ActiveSheet.ChartObjects("Chart737").Chart.SeriesCollection(1)
y = s.Values
For i = LBound(y) To UBound(y)
    Set dl = s.Points(i).DataLabel
    Select Case r
        Case Is = "Won"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            dl.Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
        Case Is = "Lost"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 5, 5)
    End Select
    Set r = r.Offset(1)
Next
End Sub