make a Table of content

[Amir666]

I want to make a Table of content in Excel 2007.
I want to have in my Table of content, sheets name with Hyperlink and with pages numbers and also all active cells in column B in all active sheets (with or without page numbers).
Do you want to help me, please?
Thanks in you advance

 

[Yongle]

all active cells in column B in all active sheets

- is that practical

What do you need?
- perhaps last used column and last used row?

OR
- are you trying to get the cell reference for the last selected cells in each sheet?
(where the cursor will appear if any sheet tab is clicked)

 

[Yongle]

OR
- are you trying to get the cell reference for the last selected cells in each sheet?

Try this code
Place it in sheet "Index" SHEET MODULE
(right-click sheet tab \ select View Code \ paste in window on right)
Run as normal - but you could trigger it with Event macro if required

Sub BuildIndex()
    Dim sht As Integer, cel As Range
    Me.Range("A:B").ClearContents
    Range("A1:B1").Value = Array("Sheet", "Active Cells")
    
    For sht = 1 To ThisWorkbook.Worksheets.Count
        With Worksheets(sht)
            .Activate
            Set cel = Me.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            cel = .Name
            cel.Offset(, 1) = Selection.Address(0, 0)
        End With
    Next sht
    Me.Activate
End Sub

 

[Yongle]

or
Try this code - Index showing Used Range in each sheet
(excludes index sheet)

Place it in sheet "Index" SHEET MODULE (as before)

Sub BuildIndex2()
    Dim sht As Integer, cel As Range
    Me.Range("A:B").ClearContents
    Me.Range("A1:B1").Value = Array("Sheet", "Active Cells")
    ThisWorkbook.Save   'resets UsedRange
    For sht = 1 To ThisWorkbook.Worksheets.Count
        With Worksheets(sht)
            If .Name <> Me.Name Then
                Set cel = Me.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                cel = .Name
                cel.Offset(, 1) = .UsedRange.Address(0, 0)
            End If
        End With
    Next sht
    Me.Activate
End Sub

 

[Amir666]

Thanks a lot,
i can't now cheak it but i think is ok.

Do you know, i have a main point with 4 subsets, if my main point was for example 4. xxxx and then i have 4 subsets like : 4.1 xxx and 4.2 xxx and 4.3 xxx and 4.4 xxxx

4. xxx is like the sheets name so.
and 4.1 xxx to 4.4 xxx are in C4, C9, C18 and C77 (but i don't know exact witch cell so i need to value of all active cells in column C)

now i thinkdo you know what is my mean.

 

[Yongle]

do you know what is my mean

No - I have no idea what you are asking

so i need to value of all active cells in column C)

- you did not mention column C or value in post#1

I want to have in my Table of content, sheets name with Hyperlink and with pages numbers

- worksheets do not have page numbers
- hyperlinks request ignored

 

[Amir666]

Ok i send to you next week my code of table of content
i can make a table of content with sheets name and pages number of each sheets but i cant find a way for find to 4.1 xxx tot 4.4 xxx
so have a good weekend and to next week.
Thank you very much