vba add a link in a sheet to another sheet in Excel

Q202. In Excel, how to add a link in a sheet to another sheet?

For example I want to have in one sheet many links to various sheets of my worksheet

To do it in Excel, here is the answer:

Easy answer is to us the INSERT HYPERLINK command

excel vba add a link in a sheet to another sheet

excel excel vba add a link in a sheet to another sheet

But if you want to have many of them then, for example if you have an index of all the sheets in the first sheet. Then doing the insert hyperlink manually will be very tedious. So here how to do it.

In one cell of the first pagem enter the following line

  1. LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
  2. MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
  3. Q201
  4. A1
  5. HYPERLINK("file:///"&C42&C43& "#'"&C44&"'!$A$1", C44)

Description:

line 1 gives you the path of the file

line 2 gives you the name of the file

line 3 is the name of the sheet

line 4 combines this plus add the name of sheet that is in line 3

inspired by

https://www.extendoffice.com/documents/excel/1150-excel-insert-path-and-file-name.html

 

You can find similar Excel Questions and Answer hereunder

1) How can I add a WorkSheet and name it as required using VBA?

2) Vba delete entire row if contains certain text in Excel

3) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

4) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

5) How can I turn off Alerts using VBA?

6) How can I delete all shapes in a WorkSheet?

7) How can I get row count of filtered data?

8) How can I dynamically add series to an existing chart using VBA?

9) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

10) I want to add a trend indicator symbol next to my sales data - how can I do that?

11) How can I clear cell after activating a routine when there is a change in value of a cell?

12) How can I add a legend to a chart using VBA?

13) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

14) How do I restrict user entry in ActiveX Text Box to just numeric values?

15) How can I sort data using VBA?