vba - Sort array size

Question

I am trying to create a hyperlink of various worksheets on a table of contents using visual basic.

The first sheet is the table of contents. The worksheets to be included in the table of contents therefore start at worksheet2. I am unablet to change the array to begin at worksheet 2.

These are my lines of code

'Create Table of Contents

 ' Create array with locations

    Set firstsheet = Worksheet(2).Value

    '  Dim arrworksheets(2 To Worksheets.Count) As Long

    For x = arr(firstsheet) To UBound(myArray)

    Set sht = Worksheets(myArray(x))

    sht.Activate

    With Content_sht

      .Hyperlinks.Add .Cells(x + 2, 3), "", _

      SubAddress:="'" & sht.Name & "'!A1", _

      TextToDisplay:=sht.Name

      .Cells(x + 2, 2).Value = x

    End With

  Next x

Thank you so much in advance!

Answer 1

As far as I can tell you are wanting to iterate through all the sheets in your workbook, but excluding the first sheet in the list - the contents page.

There's a great example of how to achieve this here, which judging by the similarity in your code, you may have already had a peek at.

The relevant part you want to look at is:

'Create Array list with sheet names (excluding Contents)
  ReDim myArray(1 To Worksheets.Count - 1)

  For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> ContentName Then
      myArray(x + 1) = sht.Name
      x = x + 1
    End If
  Next sht

Here, instead of trying to exclude the contents page based on it's order in the workbook, it excludes it based on the sheet name - If sht.Name <> ContentName.

However for completeness, if you still want to do this based on the position that each sheet is in, you can simply use a For loop instead of an array and begin at 2 (remember, sheet collections are not 0-base indexed):

Dim x As Integer
Dim count As Integer
Dim Content_sht As Worksheet
Dim sht_nam As String

count = ActiveWorkbook.Worksheets.count
Set Content_sht = Worksheets("Sheet1") 'whatever your contents page is called


For x = 2 To count

    sht_nam = Worksheets(x).Name

    With Content_sht
      .Hyperlinks.Add .Cells(x + 2, 3), "", _
      SubAddress:="'" & sht_nam & "'!A1", _
      TextToDisplay:=sht_nam
      .Cells(x + 2, 2).Value = x
    End With

Next x