+ Reply to Thread
Results 1 to 8 of 8

How to Skip Certain Sheets in a Table of Contents?

Hybrid View

  1. #1
    Registered User
    Join Date
    10-22-2012
    Location
    stockton, ca
    MS-Off Ver
    Excel 2003
    Posts
    29

    How to Skip Certain Sheets in a Table of Contents?

    I have a nifty TOC that I am pretty happy with, but I would like for the sheet list to populate without the TOC sheet in it. I would also like to apply this same idea to alphabetizing the tab order, to not include the TOC in the alphabetization of the sheet tabs. Here are the codes that I have thus far -

    Create Directory List
    Sub ListSheets()
    
    Dim ws As Worksheet
    Dim x As Integer
    
    x = 1
    
    Sheets("DIRECTORY").Range("A:A").Clear
    
    For Each ws In Worksheets
    
    Sheets("DIRECTORY").Cells(x, 1).Select
    ActiveSheet.Hyperlinks.Add _
    Anchor:=Selection, Address:="", SubAddress:= _
    ws.Name & "!A1", TextToDisplay:=ws.Name
    
    x = x + 1
    
    Next ws
    
    End Sub
    Alphabetize the tabs -
    Option Explicit
     
    Sub SortWorksheets()
         
        Dim N As Integer
        Dim M As Integer
        Dim FirstWSToSort As Integer
        Dim LastWSToSort As Integer
        Dim SortDescending As Boolean
         
        SortDescending = False
         
        If ActiveWindow.SelectedSheets.Count = 1 Then
             
             'Change the 1 to the worksheet you want sorted first
            FirstWSToSort = 1
            LastWSToSort = Worksheets.Count
        Else
            With ActiveWindow.SelectedSheets
                For N = 2 To .Count
                    If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                        MsgBox "You cannot sort non-adjacent sheets"
                        Exit Sub
                    End If
                Next N
                FirstWSToSort = .Item(1).Index
                LastWSToSort = .Item(.Count).Index
            End With
        End If
         
        For M = FirstWSToSort To LastWSToSort
            For N = M To LastWSToSort
                If SortDescending = True Then
                    If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                        Worksheets(N).Move Before:=Worksheets(M)
                    End If
                Else
                    If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
                        Worksheets(N).Move Before:=Worksheets(M)
                    End If
                End If
            Next N
        Next M
         
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646
    What's the name of the worksheet that the TOC will be on?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-22-2012
    Location
    stockton, ca
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: How to Skip Certain Sheets in a Table of Contents?

    "directory"

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: How to Skip Certain Sheets in a Table of Contents?

    It's easy to skip that worksheet in the first code.
    For Each ws In Worksheets
    
       If ws.Name <>"DIRECTORY" Then
          Sheets("DIRECTORY").Cells(x, 1).Select
          ActiveSheet.Hyperlinks.Add _
             Anchor:=Selection, Address:="", _
             SubAddress:= ws.Name & "!A1", TextToDisplay:=ws.Name
    
             x = x + 1
       End If
    Next ws
    For the alphabetizing one I think it would be easier to move the TOC worksheet to wherever you want it after the code has run.

    This would move it to be the first sheet in the workbook.
    Worksheets("DIRECTORY").Move Before:=Worksheets(1)

  5. #5
    Registered User
    Join Date
    10-22-2012
    Location
    stockton, ca
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: How to Skip Certain Sheets in a Table of Contents?

    For the alphabetizing one I think it would be easier to move the TOC worksheet to wherever you want it after the code has run.

    This would move it to be the first sheet in the workbook.
    Worksheets("DIRECTORY").Move Before:=Worksheets(1)

    I'm a little ignorant on the finer points of vba. Where would I place this code exactly?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646
    Just before End Sub in the SortWorksheets sub.

  7. #7
    Registered User
    Join Date
    10-22-2012
    Location
    stockton, ca
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: How to Skip Certain Sheets in a Table of Contents?

    Both worked perfectly, thank you. I also added another line to the sort worksheets code to keep the "SAMPLE" page where it is.

  8. #8
    Registered User
    Join Date
    11-30-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: How to Skip Certain Sheets in a Table of Contents?

    I would name it with a symbol like !!Directory , I believe it will always put it first when you aplhabetize the list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1