Vba add a link in a sheet to another sheet in Excel
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
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
- LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
- MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
- Q201
- A1
- 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