Update sheet reference for hyperlink

[bpolkusm]

Hello Everyone,

I have 111 worksheets in a workbook. Each worksheet has an area in I2- I9, K2-K9,M2-M9 O2-O5. The cell reference for each cell is the same I2 take you to B12 on sheet CV-1 and I2 on sheet CV-2 should take you to B12 on sheet CV-2 and so on. Basically each hyperlink on that worksheet takes you to a location in that worksheet. Does anyone know a way to update all the sheet hyperlinks with out doing it manually and maintaining the cell reference?

 

[EXCEL MAX]

Hello Bpolkusm,
here is code that allows you to automatically change hyperlink subaddress on each worksheet.
Suppose that all your sheets have prefix "CV-" and that only last numbers increasing.
You need to change "B12" and "I2" values in this code depeending of your needs.
Do this for each hyperlinks replacement.

Sub ReplaceLinkSubAddress()
    
    Dim vWS As Worksheet
    Dim vR1 As Range, vR2 As String

'make hyperlink on each sheet go to this cell
    vR2 = "B12"
    With ActiveWorkbook
        For Each vWS In Worksheets
            With vWS
'make this cell with hyperlink change subaddress on each worksheet
                Set vR1 = .Range("I2")
                vR1.Hyperlinks.Delete
                .Hyperlinks.Add _
                vR1, "", "'CV-" & vWS.Index & "'!" & vR2
            End With
        Next vWS
    End With

End Sub