Batch update hyperlinks page name

[whazzzzzupp17]

I have an issues I have never been able to fix. 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 hyperlinks 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?

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

 

[offthelip]

When you are renaming the sheets you need to change the references and the sheet name so they both tie up , this bit of code changes the name of "sheet1" and the references to "sheet1" in the formula in the active sheet to "New"

Sub renem()
Dim txt As String

Currentname = "Sheet1"
Newname = "New"
rono = Range("A1").CurrentRegion.Rows.Count
colno = Range("A1").CurrentRegion.Columns.Count
inarr = Range(Cells(1, 1), Cells(rono, colno)).Formula
Worksheets(Currentname).Name = Newname
For i = 1 To rono
For j = 1 To colno
   txt = inarr(i, j)

   txt = Replace(txt, Currentname, Newname)
   inarr(i, j) = txt
  Next j
Next i
Range(Cells(1, 1), Cells(rono, colno)).Formula = inarr

End Sub

hopefully this will show you how to get around your problem.

 

[whazzzzzupp17]

I saw this response late, however, I resolved it by adding a formula prior to pulling the sheet.

=HYPERLINK("#"&CELL("address",INDEX($B$10:$B$15,MATCH(A7,$B$10:$B$15,0))),A7)