Batch update hyperlinks page name

whazzzzzupp17

New Member
Joined
Jul 23, 2018
Messages
21
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?

VBA 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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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"
VBA Code:
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.
 
Upvote 0
Solution
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)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top