Dynamic Contents Page with Page Numbers

[Eriberi]

Hi All,

I am new to VBA and am really struggling to work this one out.
I am creating a document in excel which I want when printed to look like a normal word document or PDF. So far the formatting for this is going well but I have found a hiccup when trying to create a Table of Contents sheet.
At the moment I have 11 sheets in my workbook.
Sheet 1 - Information page (Where my user will put information in that will be added to different sections of the document)
Sheet 2 - Formula Information Page (Basically my dumping point for all the information in my formulas)
Sheets 3-11 - Are my final document sheets.

For the most part all of my "final document sheets" consist of one page per worksheet. However there is one worksheet (worksheet #7) which will be varying pages long depending on the information that gets added. I have got my footer page numbers to work so that no matter how many extra pages get added to worksheet #7 it adjusts the page numbers for the following worksheets.
Now I want to include these page numbers into a table of contents page. I need the table of contents page numbers to automatically update depending on the footer page numbers in each worksheet.
I have tried writing a very simple VBA code to get the information from the footer of each page into the cells for the contents page. However it does not return the actual number value it only returns the page formula (being &P).

Does anyone know how I can get this to work?

 

[kokano90]

Please reference it

Option Explicit
Sub Hyper_Link()
Dim ws As Worksheet, K&, Wss As Worksheet
Application.ScreenUpdating = False
Set Wss = Sheets("Mucluc")
    If Wss.Range("B" & Rows.Count).End(3).Row > 4 Then
        Wss.Range("A5:C" & Wss.Range("B" & Rows.Count).End(3).Row).Clear
    End If
    K = 4
        For Each ws In Worksheets
            If ws.Name <> Wss.Name Then
                K = K + 1
                Wss.Range("A" & K) = K - 4
                Wss.Range("B" & K).Value = ws.Name
                Wss.Range("B" & K).Hyperlinks.Add Wss.Range("B" & K), "", "'" & ws.Name & "'!A1"
                Wss.Range("C" & K).Value = ws.Range("C8").Value
            End If
        Next
    Wss.Range("A4:C4").Resize(K - 3).Borders.LineStyle = 1
Application.ScreenUpdating = True
MsgBox "Done"
End Sub