Create Multiple Hyperlinks

Question

How do I create a same hyperlink in all the sheets that leads to Index.

I have an Index with hyperlinks to all the sheet, but I don't have a hyperlink in all the sheets that link it back to index.

I have a 200 sheet workbook and need to have a link to the index in each and every sheet present, doing it manually would be time consuming.

I tried the copy and paste method:

1. Select the hyperlink.
2. Press Ctrl+C to copy the cell contents (the hyperlink) to the Clipboard.
3. Right-click one of the worksheet tabs at the bottom of the screen, then choose Select All Sheets.
4. Select a cell where this link will be created in all the sheets.
5. Press Ctrl+V.

The above method doesn't work. Please help.

Answer 1

The following macro (suitably modified) will do it:

Sub AddHyperlinks()

' add a hyperlink to A1 on sheet Index to B2 on  each worksheet

  Dim WS As Worksheet

  Const HyperAdd = "B2" ' the cell in which you want the hyperlink

  For Each WS In ActiveWorkbook.Worksheets

    If WS.Name <> "Index" Then

      With WS.Range(HyperAdd)

        If .Hyperlinks.Count = 1 Then .Hyperlinks(1).Delete

        WS.Hyperlinks.Add WS.Range(HyperAdd), "", "Index!A1", , "Index"

      End With

    End If

  Next

End Sub