Macro that lists, hyperlinks and lookup specific data from each sheet

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Good afternoon - I hope someone can help,


    I have an excel (2010) document that has several macros one of which creates new worksheets from a template sheet. All sheets are named "Task1", "Task2" and so on as they are created.


    What I now need is on a separate sheet ("Summary") is to create a list of all worksheets in the document with hyperlinks to those sheets. Also, from there I would like in the next column a lookup of cell B2 of each sheet. So the summary table will look something like this:


    [TABLE="width: 500"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Sheet Name

    [/td]


    [td]

    Details

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Task1 (hyperlink)

    [/td]


    [td]

    This would display the value of cell B2 from Task1

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Task2 (Hyperlink)

    [/td]


    [td]

    As above

    [/td]


    [/tr]


    [/TABLE]


    I hope this makes sense? I already have a macro that lists and hyperlinks the sheets but its the lookup of the value in cell B2 of each of those sheets that I am struggling with.


    Hope someone can help?


    Thanks in advance.

    Re: Macro that lists, hyperlinks and lookup specific data from each sheet


    You just need a simple cell reference formula in each C column cell, for example in C2 ='Task1'!B2. Without seeing how your code is looping through the sheets and creating the hyperlinks I can't post the precise code, but it should be something like this:

    Code
    Worksheets("Summary").Cells(r, "C").Formula = "='" & sheetName & "'!B2"

    where r is the row number within the loop and sheetName is the name of the current sheet in the loop, Task1 or Task2, etc.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login