Optimising code to make it run faster by eliminating sheet access

[Jimbola]

Hi all,

I have some code that generates essentially a table of contents;

    lnRow = 6
    Set wbBook = ActiveWorkbook
    'Iterate through the worksheets in the workbook and create
    'sheetnames, add hyperlink and count & write the running number
    'of pages to be printed for each sheet on the TOC sheet.
    For Each wsSheet In wbBook.Worksheets
        If wsSheet.Name <> wsActive.Name And wsSheet.Name <> "New Invoices" And wsSheet.Name <> "Template" And wsSheet.Name <> "Help" Then
            wsSheet.Activate
            With wsActive
                
                'Sheet name
                .Hyperlinks.Add .Cells(lnRow, 1), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:=wsSheet.Name
                
                'Person ID
                .Hyperlinks.Add .Cells(lnRow, 2), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!B4"")"
            
                'First Name
                .Hyperlinks.Add .Cells(lnRow, 3), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!A1"")"
                
                'Surname Name
                .Hyperlinks.Add .Cells(lnRow, 4), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!B1"")"
                
                'Provider
                .Hyperlinks.Add .Cells(lnRow, 5), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!B8"")"
            
                'Purchase Order
                .Hyperlinks.Add .Cells(lnRow, 6), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!B6"")"
                
                'Service Type
                .Hyperlinks.Add .Cells(lnRow, 7), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!D8"")"
                
                'Service Dates
                .Hyperlinks.Add .Cells(lnRow, 8), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!D6"")"
                
                'Budget
                .Hyperlinks.Add .Cells(lnRow, 9), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!F8"")"
                
                'Paid to Date
                .Hyperlinks.Add .Cells(lnRow, 10), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=sum(INDIRECT(""'"" & $A$" & lnRow & " & ""'!F12:F1000""))"
                
                'Balance Remaining
                .Hyperlinks.Add .Cells(lnRow, 11), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                TextToDisplay:="=INDIRECT(""'"" & $A$" & lnRow & " & ""'!F8"")-sum(INDIRECT(""'"" & $A$" & lnRow & " & ""'!F12:F1000""))"
            
            End With
            
            lnRow = lnRow + 1
            
        End If
        
    Next wsSheet

But as the number of sheets increases the code starts to slow down, the main slowdown happens when each sheet is activated in the loop. Is there a way to make the code run faster by eliminating the need to have the sheets accessed or any other way.