Table of Contents (Mulitple Columns)(Excludes several sheets)

[MarqyMarq]

Hey folks, I have found several examples of how to make a TABLE of Contents (single column), but I have only found one which displays multiple columns.

The code below works well, however, when I attempt to "exclude" additional sheets, I get an array error.

I am not good at arrays, so I can't seem to figure out why it is erroring out when I attempt to exclude more than 1 sheet.

There are six sheets I want to exclude from the Table of Contents: Instructions, Version_Data, Table of Contents, Summary, Tutoring Attendance, and Master. I have commented it out to make the code generate the TOC.

Here is the lines of code which I changed to exclude the six sheets: (commented out).

For Each sht In ActiveWorkbook.Worksheets
    'If sht.Name <> "Instructions" And sht.Name <> "Version_Data" And sht.Name <> "Table Of Contents" _
    And sht.Name <> "Summary" And sht.Name <> "Tutoring Attendance" And sht.Name <> "Master" Then
        If sht.Name <> ContentName And sht.Visible = True Then
          myArray(x + 1) = sht.Name
          x = x + 1
        End If
    'End If
  Next sht

Here is the code to create the TOC: The Set sht = Worksheets(myArray(x)) is where the code errors out.

'Create Table of Contents
  x = 1
  For y = 1 To ColumnCount
    For z = 1 To WorksheetFunction.RoundUp(shtCount / ColumnCount, 0)
      If x <= UBound(myArray) Then
        Set sht = Worksheets(myArray(x))
        sht.Activate
        With Content_sht
          .Hyperlinks.Add .Cells(z + 3, 2 * y), "", _
          SubAddress:="'" & sht.Name & "'!A1", _
          TextToDisplay:=sht.Name
        End With
        x = x + 1
      End If
    Next z
  Next y[CODE=vba]

The end result works well for a multiple column TOC, which is alphabetized and divided equally across all the columns. Just can't get the code to exclude the names in YELLOW below.

Any help would be appreciated.

 

[Alex Blakenburg]

I am afraid that giving us a simplified version doesn't work in most cases.
The section you are trying to change does not in fact drive what gets included in the Table of Contents.

I believe that your original source is this.
Automate Building A Table Of Contents For Your Spreadsheet With VBA — TheSpreadsheetGuru

The section you are trying to change should be reinstated to the original.
The 2 pieces of code that need to be change in order of appearance are in the ones below.

Rather than hard code names I have relied on the fact that all the sheets that you want included have a comma in the sheet name.
We would need to change that logic if you don't think we can rely on that being the case.

    'Count how many Visible sheets there are
    ' XXX Changed to only select sheets containing a "," in the sheet name
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible = True And InStr(sht.Name, ",") > 0 Then shtCount = shtCount + 1
      Next sht

    'Create Array list with sheet names (excluding Contents)
      ReDim myArray(1 To shtCount)
    ' XXX Changed to only select sheets containing a "," in the sheet name
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> ContentName And sht.Visible = True _
                And InStr(sht.Name, ",") > 0 Then
          myArray(x + 1) = sht.Name
          x = x + 1
        End If
      Next sht

 

[MarqyMarq]

Hello Alex! Again, your solution is spot on!

What I am gathering in reviewing your code, is the original error may have been in the "Count how many Visible sheets there are" lines of code.

You are also very perceptive in identifying where I had retrieved that code base. My lines of code very long and I didn't want to take away from the actual problem I am facing.

In my mind, by keeping it short, and to the point, I wanted the experts to focus on the root cause. Lesson learned for me... always include the whole code!

I tried to apply a CASE Selection in my initial alternative solution, but kept having the same error.

Question: I have the line of code below throughout my program. Is there an easy way to "hard code" it which allows me to add additional (admin) tabs to the existing list (ADMIN_SHEETS)?
Again, all these Admin tabs to do not get sorted, rearranged, or acted upon when retrieving information from individual student data sheets.

If sht.Name <> "Instructions" And sht.Name <> "Version_Data" And sht.Name <> "Table Of Contents" _
    And sht.Name <> "Summary" And sht.Name <> "Tutoring Attendance" And sht.Name <> "Master" Then

I am thinking:

       If sht.name <> ADMIN_SHEETS then
                     ...action ....
                     ...action ....
       End If

Does it become a global variable or function? Not sure how I would apply that approach.

Thanks again Alex!

 

[Alex Blakenburg]

To minimise maintenance you want to set it up as a variable "once" early on in the code.
See if the below helps. I have used a semi-colon separator only because you have sheets with a comma in them.

Note: Keep in mind that in the below you are just doing a find in string, so if there is a sheet just called Data it would find it in the string which may not be what you want.

Sub testExclude()

    Const excludeSheets As String = "Instructions;Master;Summary;Tutoring Attendance;Version Data"
   
    Dim sht As Worksheet
   
    For Each sht In ActiveWorkbook.Worksheets
   
        If InStr(1, excludeSheets, sht.Name, vbTextCompare) = 0 Then
            'Do stuff
            MsgBox sht.Name & " - Not in Excluded List"
        Else
            'Do not need the else not doing anything here
            MsgBox sht.Name & " - To be Excluded"
        End If
    Next sht
   
End Sub