'Fixing' an Index Sheet

[stuartgb100]

I have a workbook containing a large number of sheets.
To more easily navigate through the book, I have an Index sheet containing hyperlinks to all the various sheet names.
This lets me 'jump' to the required sheet, and works well.
However, after 'jumping', the index sheet is no longer in view (is that in the taskbar - not familiar with the terminology !)
So I have to go back to the beginning to show all sheet names, before I can see 'Index' and click on it for the next required sheet.

Is there a way to 'fix' this so that the sheet 'Index' tab is always visible ?
Thanks.

 

[stuartgb100]

Ooops typo:
meant Workbook BeforeClose.

 

[Peter_SSs]

Can you explain what is happening please ?

When you are adding the hyperlink you are not referencing the Anchor to the Index sheet so the Anchor was going on the active sheet.
I have not fully tested the code but try making this change

Sheets("Index").Hyperlinks.Add Anchor:=Sheets("Index").Range("A" & i), Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name

 

[stuartgb100]

Thanks again, that seems to be working.
I'll continue to 'test'.
I thought that it was 'losing' the Index sheet, but what threw me was that when I used 'Test' (as mentioned above) it ran on the active Vis sheet AND the Index sheet.

Thanks again,
Cheers.

 

[Peter_SSs]

it ran on the active Vis sheet AND the Index sheet.

Only "sort of" I believe. I think that if you remove the extra code we just added and went through the process again of closing when say "Today" (let's use your actual sheets names as examples since they are listed in the code) was the active sheet you would find that whilst both 'Today' and 'Index' appear to have the hyperlinks, I don't think any of those hyperlinks would actually work.

In relation to the cells in column A of Index, they appear to be hyperlinks because your Workbook_Open code uses this line

Worksheets("Index").Columns(1).ClearContents

That clears the text from the cells but not the (blue/underline) formatting. I would recommend using this line instead.

Worksheets("Index").Columns(1).Clear

 

[stuartgb100]

I understand thanks, and will change the code accordingly.

I tested with the 'old' code after I had posted, and I found the same.
Your explanation clears things up.

Thanks for being patient.
Cheers.

 

[Peter_SSs]

Cheers. No problem.