Create a hyperlink from a cell on multiple sheets to a specific sheet

Asked Mar 28 '22 at 16:06

Active Mar 28 '22 at 16:08

Viewed 15 times

I have 339 sheets called Matrix1, Matrix2, Matrix3,..., up to Matrix 339 and for each of those sheets I need to create a hyperlink in cell A2 that takes me to a specific sheet called Index (it doesn't matter in which cell ). I also need cell A2 for each of the Matrix worksheets to have the corresponding number. For example, if I am in the Matrix20 sheet, cell A2 has the corresponding number 20 as its name and would have the hyperlink to the Index sheet. So consecutively for the other matrix sheets.

My code so far is this one:

Sub Hyperlink()
For i = 1 To 339
Worksheets("Matrix" & i).Cells(2, 1) = "=HYPERLINK(""[Workbook.xlsx]Index!A1"",i)"
Next
End Sub

But is not working. I don't know why. Thanks in advance for the answers.

edited Mar 28 '22 at 16:08

Mayukh Bhattacharya

12,541
5
21
32

asked Mar 28 '22 at 16:06

LOUIS SKINNY

2

Currently `i` falls inside the quotes, but it should be *outside* them, and concatenated with `&`. – BigBen Mar 28 '22 at 16:10
2

`"=HYPERLINK(""[Workbook.xlsx]Index!A1""," & i & ")"` – BigBen Mar 28 '22 at 16:17
2

It works with a slight modification. Thanks, friend. – LOUIS SKINNY Mar 28 '22 at 16:26
2

I'd also add `ThisWorbook.` before `Worksheets`, as well as an `.Formula` (or `.Formula2` if your version of Excel allows it) after `.Cells(2, 1)`. – BigBen Mar 28 '22 at 16:28

Create a hyperlink from a cell on multiple sheets to a specific sheet

0 Answers0