Loop to Table of Contents is skipping rows

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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.
 
Solution

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,939
Messages
5,639,093
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top