Add worksheets subtotal hyperlink to indexsheet starting from B1

0

Dear Sirs

I have the following macro that create hyperlink of every sheet in to Indexsheet (Contents Sheet) .

The hyperlink in Content Sheet start

  • at A1 (it display the name of sheet 1) 
  • at A2 ( it Display the name of sheet 2)
  • at A3 ( it Display the name of sheet 3)

Every worksheets have subtotal in E4.

I need the help in modyfing this macro to include subtotal summary hyperlink of every sheets into Column B of Contents sheet

So 

  • B 1 hyperlink to Subtotal at E4 of sheet 1(It Display subtotal Value at E4 of sheet1)
  • B2  hyperlink to Subtotal at E4 of sheet 2 (It Display subtotal Value at E4 of sheet2)
  • B3  hyperlink to Subtotal at E4 of sheet 3 (It Display subtotal Value at E4 of sheet3)

Looking forward to having your help in this regards

Many thanks and best regards

Arsil Hadjar

Sub CreateIndexSheet()
    Dim wSheet As Worksheet
    ActiveWorkbook.Sheets.Add(before:=Worksheets(1)).Name = "Contents" 'Call whatever you like
    Range("A1").Select
    Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
    For Each wSheet In Worksheets
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name
        ActiveCell.Offset(1, 0).Select 'Moves down a row
    Next
    Range("A1").EntireColumn.AutoFit
    Range("A1").EntireRow.Delete 'Remove content sheet from content list
    'Add Return to Content in each sheets
    ReturnToContentAllsheets
    Application.ScreenUpdating = True
End Sub
Answer
Discuss

Answers

0
Selected Answer

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.

Discuss

Discussion

Dear Variatus,
The reason for I want a macro to fetch the value of a cell from another sheet is a actually in my workbook I have 17 worksheets  with different data, and each sheets has subtotal in E4.

I know that The formula below, placed in B1 of my ContentSheet should do the job. 

But using this methods I will need to do 17 repetition to fetch the value of a cell from another sheet
I think using using macro will help in solving this repetation job and faster.

Best Regards
Arsil Hadjar

Select All
='Sheet1'!$E$4
Arsil (rep: 24) Oct 31, '18 at 7:56 pm
Dear Variatus,

I have tried your alternative methods as per your suggestion using the INDIRECT Function below , It works perfectly to fetch the subtotal value in Cell E4.
But there is no hyperlink to that subtotal value
 
I appreciate all of your assistance with this alternative methods.  Your suggestions definitely brought more ideas to mind

I am still looking on the  possibility of using macro for this purpose  to enhance my knowledge in macro.

Looking forward to having your further assistance in this regards

Best regards
Arsil Hadjar


=IFERROR(INDIRECT("'"&$A1&"'!E4"),"")
Arsil (rep: 24) Oct 31, '18 at 9:09 pm
You will not need 17 repetitions. You should copy the formula from here into B1 of your ContentSheet and then copy it from B1 to B2:B25. After that you can forget it. Whether you have 17 sheets, 7 or 25 sheets the total will always be shown. For non-existent sheets the cell will be shown as blank. No need to change your macro.
Variatus (rep: 2613) Nov 1, '18 at 12:57 pm
Dear Variatus,

Many thanks for the help , The code both set the hyperlinks and copy the totals, but still not creating hyperlink to SubTotal at E4 in source worksheets.

How to include the hyperlink to subtotal in this regards

Looking forward to having your further advice in this regards

Best regards
Arsil Hadjar
Arsil (rep: 24) Nov 1, '18 at 8:03 pm
Aah! I hadn't understood that requirement. Now it is included in the amended answer above.
Variatus (rep: 2613) Nov 2, '18 at 5:19 am
Dear Variatus,

Thanks a lot for the help and assitance . It works.

Best regards
Arsil Hadjar
Arsil (rep: 24) Nov 4, '18 at 7:56 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login