Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Reorder Hidden Sheets

  1. #11
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    Quote Originally Posted by white84 View Post
    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!

  2. #12
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    Quote Originally Posted by My Aswer Is This View Post
    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

    Code:
    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!!!

  3. #13
    New Member
    Join Date
    May 2018
    Location
    Troy, Ohio
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reorder Hidden Sheets

    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •