Copy Sheet to Same Workbook but Update Hyperlinks to New Sheet Name

Hi Community,

I am desperate to get some help. I'm not an Excel fundi and have a serious issue. I created a sheet "Sheet1" and filled it with content. There are 100's of hyperlinks to the same sheet used for navigation. I have .png arrow all over the sheet to move to different places in the sheet, e.g. "Back to Top" etc. I did not know this would happen and there are a lot of links. I need to duplicate the page 12 times. It will take days to redo every for all 12 months of the year. I really need an easier way than manually changing every single image based hyperlink.

Practically, I need a way to rename "Sheet1" to "January" and copy it to the same workbook for "February", "March" etc. - The issue is obviously as soon as I rename "Sheet 1" to "January" all the links display "Reference not Valid" as it is linked to "Sheet1". Also if I create a copy of "Sheet1" and name it "February" the links go back to "Sheet1" in the same workbook.

There must be a way to link hyperlinks to new sheet name?

I would really appreciate some help on this as I have a serious deadline and have been searing for a solution online for the last 7 hours with no success.

Thank you in advance

hi
sorry if this is too late for your deadline
it should be possible to do this with a macro. For example, this macro should change the links in the active sheet from "sheet1" to "January"
if it doesn't work, could you give a bit more info on the sheets you want to link to and from, and the type of hyperlink used? If you could upload a workbook with some examples of what you want that would be even better

Sub Hyperlinks_change()
Dim OldName As String, NewName As String, HL As Hyperlink
OldName = "Sheet1"
NewName = "January"

For Each HL In ActiveSheet.Hyperlinks

HL.SubAddress = Replace(HL.SubAddress, OldName, NewName)
HL.Address = Replace(HL.Address, OldName, NewName)
HL.TextToDisplay = Replace(HL.TextToDisplay, OldName, NewName)

Next HL
End Sub

Hi Nicky,

Thank you, you are a star! I would go as far as saying you are a hero among ordinary folks! I truly appreciate you help!