How can I add a dynamic contents page using VBA?

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
I have a spreadsheet which contain data across 15 sheets and each sheets holds specific data and charts for all organisations within the company. That is used for creating a report and I currently have a macro that loops through all the organisations within the company, selects the relevant pages, deletes all the superfluous data for the organisations not being reported on and then exports it to a PDF file. This all works perfectly, but now I have been asked to insert a contents page at the start (with hyperlinks if possible) so that this then feeds through to the PDF and I am having trouble with this bit.

As the data that is deleted is different depending on the organisation currently being reported on, the page counts will always change, and some of the sheets have sections that would need different hyperlinks and page numbers within it, so I can't just use the sheet as the start of a new section, which is how I've got it working at the moment:
Code:
Sub PgCount()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet

Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Dim PgNo As Long

Set wbBook = ActiveWorkbook
Set wsActive = wbBook.ActiveSheet
lnRow = 2
lnCount = 1
PgNo = 3

For Each wsSheet In wbBook.Worksheets
    If Right(wsSheet.Name, 5) = "(PDF)" And wsSheet.Name <> "1 (PDF)" And wsSheet.Name <> wsActive.Name Then
    'If wsSheet.Name <> wsActive.Name Then
        wsSheet.Activate
        With wsActive
            .Hyperlinks.Add .Cells(lnRow, 1), "", _
            SubAddress:="'" & wsSheet.Name & "'!A1", _
            TextToDisplay:=wsSheet.Name
            lnPages = wsSheet.PageSetup.Pages().Count
            .Cells(lnRow, 2).Value = "'" & PgNo 
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
        PgNo = PgNo + lnPages
    End If
Next wsSheet

End Sub

The code I have will just create a list with the relevant page number, but will just list the sheet name as the section and this is wrong. Is there a way to feed the information I have into an existing layout for the table of contents, and then also for it to pick out the section headers?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top