update all cell links on sheet after sheet name change

  • I have a code that copies many sheets to a new master workbook and then renames the sheets on the master file.


    However, all the cell links from the sheets copied over no longer work, because the page name has changed. How can I go about renaming the hyperlink string for the page name? These are just links from the cell.value to the location at where there are on a table of data.


    For example I have a small data set - lets say A1:A15 that have a name and have links to a table location on the same sheet based on each cells values.


    After copying the sheet to the master workbork and renaming the sheet on the master workbook, the hyperlink for the sheet name doesn't change. If I don't rename the sheet it works fine, but I need to keep the new names..


    Here's what I have so far

    Code
    Sub RenameHyperlinks()
    Dim i as Long
    Dim SheetCount as Long
    Dim ws as worksheet
    SheetCount = Sheets.Count
    i=11 'since I have 10 fix sheets and I add sheets after 10
    For ws = i to SheetCount

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!