May 29 2020 08:15 AM
I have created a contents list of all the tabs in the workbook using VBA code with hyperlinks.
The problem is that if the file name changes, or it gets moved the hyperlink no longer works.
If you use the "=HYPERLINK("#Sheet2!A1","Sheet2") formula in a cell it creates a link that is independent of the file path address or name. Using the VBA code below I cannot find a way of creating a similar hyperlink without the full file path. I have also tried to use the VBA code to create the above formula but it will not work for me as a result of the "#" character.
ActiveCell.Offset(3, 1).Activate
For Each wSheet In Worksheets
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
ActiveCell.Offset(1, 0).Select 'Moves down a row
Next
What is best to sort this out.
Thanks
rob
May 31 2020 08:01 PM
@Rob_embry Have you tried to wrap your codes with "With ThisWorkbook" and "End With"?
Alternatively if you want to use Hyperlink formula in your VBA:
With ThisWorkbook
ActiveCell.Formula = "=HYPERLINK('" & wSheet.Name & "'!A1," & Chr(34) & wSheet.Name & Chr(34) & ")"
End With