Loop to Table of Contents is skipping rows

[CalRich1023]

Hello,

I have a macro to loop through all of the sheets in my workbook and return those sheet names with hyperlinks and some more additional info to a "table of contents" sheet. But i have it skipping some of the sheets that I don't want included in the TOC. As a result, it is leaving blank rows on my Table of Contents. I'm wondering how to stop that from happening?

Dim int1 As Integer

For int1 = 1 To Worksheets.Count
If Worksheets(int1).Name <> "Table Of Contents" And Worksheets(int1).Name <> "SEARCH" And _
Worksheets(int1).Name <> "ACCT #'S" And Worksheets(int1).Name <> "DATA" And _
Worksheets(int1).Name <> "COPY TEMPLATE" Then 'SKIP THESE SHEETS

'Else 'MAYBE DON'T NEED THIS - WORKS WITHOUT IF ABOVE SHEET NAMES ARE <>
'BUT THEN SKIPS ROWS WITH RESULTS.
'SHOWS WORKBOOK NAME - POSSIBLY CHANGE LATER ON TO A LOCATION?
Cells(int1 + 1, "A").Value = myworkbookname1
'INSERTS SHEET NAMES WITH HYPERLINKS - SHEET NAME MUST BE 1 WORD(NO SPACES OR HYPHENS)
Sheets("Table Of Contents").Hyperlinks.Add Anchor:=Cells(int1 + 1, "B"), Address:="", _
SubAddress:=Worksheets(int1).Name & "!A1", TextToDisplay:=Worksheets(int1).Name
Cells(int1 + 1, "C").Value = Sheets(int1).Cells(4, "F") 'INSERTS INFO 1
Cells(int1 + 1, "D").Value = Sheets(int1).Cells(4, "C") 'INSERTS INFO 2
Cells(int1 + 1, "E").Value = Sheets(int1).Cells(5, "F") 'INSERTS INFO 3
Cells(int1 + 1, "F").Value = Sheets(int1).Cells(6, "F") 'INSERTS INFO 4
Cells(int1 + 1, "G").Value = Sheets(int1).Cells(7, "F") 'INSERTS INFO 5

End If
Next int1

 

[Fluff]

How about

Sub CalRich()
   Dim Ws As Worksheet
   Dim i As Long
  
   i = 1
   For Each Ws In Worksheets
      Select Case Ws.Name
         Case "Table Of Contents", "SEARCH", "ACCT #'S", "DATA", "COPY TEMPLATE"  'SKIP THESE SHEETS
         Case Else
            i = i + 1
            With Sheets("Table of Contents")
               .Cells(i, 1) = myworkbookname1
               .Hyperlinks.Add .Cells(i, 2), "", "'" & Ws.Name & "'!A1", , Ws.Name
               .Cells(i, "C").Value = Ws.Cells(4, "F") 'INSERTS INFO 1
               .Cells(i, "D").Value = Ws.Cells(4, "C") 'INSERTS INFO 2
               .Cells(i, "E").Value = Ws.Cells(5, "F") 'INSERTS INFO 3
               .Cells(i, "F").Value = Ws.Cells(6, "F") 'INSERTS INFO 4
               .Cells(i, "G").Value = Ws.Cells(7, "F") 'INSERTS INFO 5
            End With
      End Select
   Next Ws
End Sub

This will also allow sheet names to have spaces.

 

[CalRich1023]

THIS WORKS PERFECTLY!
As you can probably tell, I'm rather new to VBA coding, but definitely learning a lot here. The Select Case seems to be a vary useful tool in my current project.
Thanks so much for your help and quick response!

 

[Fluff]

Glad to help & thanks for the feedback.