Posts by DustoMan

    I'm trying to do kinda the same thing. There are some cells in my worksheet that link to last years workbook. I need to change all the hyperlinks so that they point to this year. For example:

    The 03s need to be changed to 04s. I've tried to adapt the code posted by bnix to do this, but my VB is kinda rusty. Right now it looks like this:

    Sub ChangeAllHyperLinks()
    Dim strOldYear As String 'old year
    Dim strNewYear As String 'new year
    strOldYear = "03"
    strNewYear = "04"

    Dim H As Hyperlink
    For Each H In ActiveSheet.Hyperlinks 'only looks in active sheet
    'this assumes you are using three letter naming convention for your months
    'if your text does not display the month and range you can change the TextToDisplay part to H.Range
    'I used the H.TextToDisplay to make sure it was changing as it should
    If Left(H.Address, 2) = strOldYear Then
    'set the new link to new month and keep current range value i.e., !A1
    H.Range = strNewYear & Right(H.Range, Len(Left(H.Range, 2)))
    'also changes the sub address of link to new month and keep current range value
    H.SubAddress = "" & strNewYear & Right(H.Range, Len(Left(H.Address, 2))) & ""
    End If
    End Sub

    Can someone help me with this?