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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,740
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Solution

whazzzzzupp17

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

Watch MrExcel Video

Forum statistics

Threads
1,127,876
Messages
5,627,418
Members
416,245
Latest member
Xterminat

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
Top