Inserting an hyperlink for the sheet path?

[gaftalik]

Hi all ,

I would like to insert an hyperlink in a sheet which links to a sheet in another book .
So what is the formula to apply in the 2nd book sheet cell in order to get the whole path of the sheet itself?

Thank you very much

 

[Tazguy37]

I'm a little confused as to what you're asking for.

Do you want a formula that creates a hyperlink (the HYPERLINK() function can do this), or a formula to show the complete path of the worksheet you're in?

Please explain in a little more detail,, and I'm sure we can come up with something.

 

[gaftalik]

I mean that , could the sheet have a path itself so that when clicking on the hyperlink the file will open directly to that specific sheet and not any sheet inside.If this is possible how to obtain the full path to open that needed sheet ?

Thank you

 

[Tazguy37]

I don't think a hyperlink can go to a specific place in a different workbook. What you can do is, when that second workbook opens, use some VBA code in the Workbook_Open() event, so every time that workbook is opened, a certain worksheet (or ever cell) is visible.

Does that sound like what you're after?

 

[gaftalik]

So i understand that hyperlinks could not open a specific sheet each time the workbook opens !
Can i get that VBA code please and how to apply it in details?

Thank you very much .

 

[Tazguy37]

You can use the following code to go to Sheet2 every time the workbook is opened:

Private Sub Workbook_Open()
    Sheets("Sheet2").Activate
End Sub

Make sure you change Sheet2 to the name of your worksheet.

If you don't know the name of the worksheet (or if it might change), but you know it's always going to be the 2nd tab, use this instead:

    Sheets(2).Activate

Since this is a Workbook event, you'd put this code in the ThisWorkbook module.

Is that what you're after? Hope that helps!

This code doesn't go in a standard module, but the ThisWorkbook module.

 

[gaftalik]

Hi Tazguy,

I tried to paste your codes by clicking the excel icon beside the file on the menu toolbar after saving and reopening it, gave me for each code an error (9)report saying subscript out of range .

What went wrong ?

 

[Tazguy37]

What went wrong ?

I messed up...

See my edited post. Sorry about that!