Automatically create a list of all sheets and hyperlink to them

[James bolton]

Hello,

I'm currently working on a workbook that has like 60 separate worksheets and will continue to be added to each month (one worksheet for each client that is updated onto a summary)

Is it possible to create like a "content" page that automatically lists every worksheet and on click hyperlinks to it ?

 

[Hui]

Have a read of:
https://msdn.microsoft.com/en-us/library/office/ff841199.aspx

 

[James bolton]

Hi

Thanks, the links very useful

However - is there a way to one up this?

I'd like it so that everytime you re-ran the report, (or possibly via an update button) it just adds to the list or updates it, instead of creating a brand new sheet each time.

It would be great if I could add formula's next to the table of contents to summarize certain things.

 

[Hui]

You can add code that will run it when something happens
Why not post a sample file with s sample of when it should run?

 

[James bolton]

Yes,

Please see attached a sample spreadsheet.

I have the create TOC VBA to create the TOC in the 1st place, I've added a vlookup formula next to the data, however, if I add new worksheets and rerun the create TOC VBA, it deletes the sheet and creates it from scratch all over again, I'd like it to just amend the TOC with changes whenever I create a new sheet not overwrite it.

 

[Hui]

What I have done is changed the code so that it clears the TOC worksheet
and then recreates it everytime you select it
It also creates the new formula for you at the same time

see attached