Inserting an hyperlink for the sheet path?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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 .
 
Upvote 0
You can use the following code to go to Sheet2 every time the workbook is opened:

Code:
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:

Code:
    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.
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top