Table of Contents - Data from unhidden sheets only

[Guest]

I have a financial planning workbook with 30 sheets. The book has a table of
contents that is created manually by pulling info from cell A1 of each sheet.
However, we hide certain sheets depending on the situation. The sheets that
are hidden in any given situation is unpredictable.

Is there a way to automate pulling data only from the unhidden sheets and
then putting it onto a table of contents page?

 

[KL]

Hi,

Try this:

Sub TEST()
Dim ws As Worksheet
Dim i As Long
With ActiveWorkbook
For Each ws In .Worksheets
Select Case True
Case ws.Visible And ws.Name <> "Contents"
.Sheets("Contents").Cells(i + 1, 1) = _
ws.Range("A1")
i = i + 1
End Select
Next
End With
End Sub

Regards,
KL

 

[Guest]

That worked fantastic. Once I figured out how to leave blank lines, it was
perfect. Thank you.

Now, if it is not pushing it, is it possible to match the page numbers for
the pages that the titles are on to the titles pulled into the table of
contents page. The page numbers would be in column

 

[KL]

Hi Scott,

See if something like this does what you want:

Sub TEST()
Dim ws As Worksheet
Dim i As Long
With ActiveWorkbook
For Each ws In .Worksheets
Select Case True
Case ws.Visible And ws.Name <> "Contents"
.Sheets("Contents").Cells(i + 1, 1) _
= ws.Range("A1")
.Sheets("Contents").Cells(i + 1, 2) _
= ws.Name
.Sheets("Contents").Cells(i + 1, 3) _
= i + 1
i = i + 1
End Select
Next
End With
End Sub

Regards,
KL

 

[Guest]

Thanks for the response. I pasted the new language and now am getting the
worksheet name on the table of contents as well as info from cell A1 on each
sheet. So I took out

".Sheets("Contents").Cells(i + 1, 2) _
= ws.Name"

That got rid of the ws name.

Our workbook is set up so that some worksheets have a 1 page printout, some
2 and others as many as 10. This varies from one workbook to the next,
dependning on the client.

The page numbers in the script you sent me seem to be consecutive and not
based on the actual number of pages a sheet will print. Is it possibe to
write it so that it the table of contnets is completed with the correct page
references?

Thanks again for your help.