Dynamic Hyperlink for Excel Tab Names 2

[VisaManiac]

I have this spreadsheet with lots of worksheets that is constantly being updated. I'd like for my first worksheet to list the names of all the worksheets, as well as hyperlinks to each related worksheet. This is all I have so far to create the list of tab names when I run the macro, but don't know how to create hyperlinks to each worksheet as well.

Sub ListingTabNames()
Dim Ws As Worksheet, Wb As Workbook, R As Range, I As Integer
Set Wb = ActiveWorkbook
Set R = ActiveSheet.Range("a1")
I = 1
For Each Ws In Wb.Worksheets
R.Cells(I, 1) = Ws.Name
I = I + 1
Next Ws
End Sub

Any help on this would be greatly appreciated!

 

[SkipVought]

Hi,

Have you tried Insert > Hyperlink in conjunction with your macro recorder?

Please post further responses and any other VBA questions in Forum707.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Salut39]

Hi,

I can give you example how I've done it:
I have my tabs named: 001, 002,003...
Cell A2 = 001
Cell A3 =

=HYPERLINK("["&MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)&"]'"&TEXT(A2+1,"000")&"'!A1",TEXT(A2+1,"000"))

Looks a bit complex but it make the work done.

Yuri

 

[GlennUK]

Hi Yuri,

this formula delivers the same functionality as your formula:

=HYPERLINK("#"&TEXT(A2+1,"000")&"!a1",TEXT(A2+1,"000"))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Salut39]

Thanks Glenn,

Great staff!!! I am always for easy once.

Yuri

 

[SkipVought]

You know that you can also Right-Click the Workbook Navigation Arrows in the lower LH corner of the application, and you'll get a Sheet List.

Sure beats, selecting sheet 1, which may be out of sight, and finding the appropriate HL!!!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Salut39]

I know this one Skip. But I have more than 200 worksheets. I made a reversed hyperlink on every sheet to bring me back to Header Sheet.

Still though can't get it how "#" works, as a wildcard?
Could you or Glenn explain the logic here if possible so I can use it in the future?

 

[GlennUK]

The "#" is shorthand for "this workbook" ... I can't remember when I first came across it, but I've used it for a long time.

From Jon Peltier's site:

Hyperlink to a Worksheet Range

This is almost a trivial exercise. Enter the cell reference, or select a defined name. If you browse to another workbook, Excel shows an Address field in the Insert Hyperlink dialog to the path and file name of the linked workbook. To link to a particular location in the other workbook, click on the Bookmark button. Now browse to the desired location as you would within the same workbook. Excel appends the sheet name and cell reference to the workbook path and name in the Address field:

C:\My Documents\Book.xls#Sheet1!A1

( from

http://peltiertech.com/Excel/Hyperlinks.html

)

See how the link is workbook#sheet!cell ? Well, leaving out the workbook name, and just having the "#" makes Excel assume that the workbook containing the formula is the one to use.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Salut39]

It's so clever. That's what I thought it was. Any other useful shorthands for worksheet name, perhaps?

Thanks for explanation!