Add worksheets subtotal hyperlink to indexsheet starting from B1

Not clear why you should want a macro to fetch the value of a cell from another sheet. The formula below, placed in B1 of your ContentSheet should do the job.

='Sheet1'!$E$4

Replace "Sheet1" with the actual name of the sheet. Note that the single quotation marks (apostrophes) are only required if the sheet name includes blanks. You can enter them and Excel will remove them if they are superfluous. This formula couldn't be copied down because the sheet name is different in each row.

The formula below would allow you to enter it once and copy down provided that A1 contains the name of the worksheet. This formula would also allow you to change the sheet names. B1 would show the value of E4 of the sheet the name of which is given in A1.

=INDIRECT("'"&$A1&"'!E4")

Finally, if you aren't sure that A1 will contain the name of an existing sheet you might use the formula below which will show nothing if the sheet name in A1 is invalid in whatever way.

=IFERROR(INDIRECT("'"&$A1&"'!E4"),"")

So, if the number of sheets in your workbook is different from one run of the macro to the next, copy the formula down for more rows than you will ever need and the superfluous ones will be ignored, showing blank cells.

Edit 02 Nov 2018   ==================================

The code below will both set the hyperlinks, copy the totals and set a hyperlink to the source of each total.

Option Explicit

Private Enum Ndc                ' Dashboard columns
    ' 01 Nov 2018
    NdcTab = 1                  ' 1 = column A
    NdcSum
End Enum

Sub CreateIndexSheet()
    ' 02 Nov 2018

    Const DashName As String = "Content"        ' change as desired
    Const SumSource As String = "E4"            ' change as required

    Dim Wb As Workbook
    Dim Dashboard As Worksheet
    Dim Ws As Worksheet
    Dim R As Long

    Set Wb = ActiveWorkbook                     ' dangerous! Better specify a workbook by name,
                                                ' for example:-
                                                ' Set Wb = Workbooks("TXL 181101 Dashboard Code.xlsm")
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    With Wb
        On Error Resume Next
        .Worksheets(DashName).Delete        ' delete pre-existing Dashboard
        On Error GoTo 0
        Set Dashboard = .Sheets.Add(Before:=Worksheets(1))
        Dashboard.Name = DashName
    End With

    With Dashboard
        For R = 1 To Wb.Worksheets.Count - 1
            Set Ws = Wb.Worksheets(R + 1)
            .Hyperlinks.Add Anchor:=.Cells(R, NdcTab), _
                                     Address:="", _
                                     SubAddress:="'" & Ws.Name & "'!$A$1", _
                                     TextToDisplay:=Ws.Name
            .Cells(R, NdcSum).Formula = "=INDIRECT(""'" & Ws.Name & "'!" & SumSource & """)"
            .Hyperlinks.Add Anchor:=.Cells(R, NdcSum), _
                                     Address:="", _
                                     SubAddress:="'" & Ws.Name & "'!" & SumSource
        Next R

        For R = NdcTab To NdcSum
            With .Columns(R)
                .AutoFit
                .ColumnWidth = Round(.ColumnWidth + 3)
            End With
        Next R
    End With

    ReturnToContentAllsheets                'Add Return to Content in each sheets
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Apart from the addition you requested it is a copy of your original code in its essential parts but reformatted to simplify modification by a lay person. One such modification, concerning the workbook on which the code runs, I strongly recommend. Please read the comments.