Forum Discussion
Solved
Excel - mass changing hyperlinks
Using Microsoft Professional Plus 2013
I have a spreadsheet created from an Oracle query in TOAD. The results are considered Detail. I then insert a Summary sheet for each Detail sheet. I do counts, etc. The Detail sheets have about 4000 rows each.
The big issue I have now is I have hyperlinks on the Summary page that reference data in a Detail sheet (i.e. ='Detail Sheet'!G397). The hyperlink on the Summary page should point to the cell next to it (H397). The hyperlink menu comes up on the default (Link to Existing File or Web Page > Current Folder). I need to have it go to the Detail Sheet cell H397. I tried to record this process as a Macro, but have forgotten all my macro skills from years ago. Is there an easy way to do this? I can't post the spreadsheet because it's classified government data, so I'd have to do a mock up, if that would help. Thank you in advance.
Recording a macro to change the hyperlink in one cell will give you:
Range("E5").Select Selection.Hyperlinks(1).SubAddress = "Detail!H3"To change every hyperlink in column E you will need to:
- Loop through each cell in column E
- Check if the cell has a hyperlink because trying to change a non-existent hyperlink will throw an error
- Change the hyperlink to something that can be expressed in a formula based on where the cell is within column E (i.e. its ROW)
Based on your Dummy workbook it seems that hyperlink is to a cell two rows above in column H of the Detail sheet. The following code limits the loop to the UsedRange because looping through all 1048576 is inefficient.
Sub EditHyperlinks2() Dim cel As Range With ThisWorkbook.Sheets("Summary") For Each cel In Intersect(.[E:E], .UsedRange) If cel.Hyperlinks.Count > 0 Then cel.Hyperlinks(1).SubAddress = "Detail!H" & cel.Row - 2 End If Next End With End Sub
4 Replies
Recording a macro to change the hyperlink in one cell will give you:
Range("E5").Select Selection.Hyperlinks(1).SubAddress = "Detail!H3"To change every hyperlink in column E you will need to:
- Loop through each cell in column E
- Check if the cell has a hyperlink because trying to change a non-existent hyperlink will throw an error
- Change the hyperlink to something that can be expressed in a formula based on where the cell is within column E (i.e. its ROW)
Based on your Dummy workbook it seems that hyperlink is to a cell two rows above in column H of the Detail sheet. The following code limits the loop to the UsedRange because looping through all 1048576 is inefficient.
Sub EditHyperlinks2() Dim cel As Range With ThisWorkbook.Sheets("Summary") For Each cel In Intersect(.[E:E], .UsedRange) If cel.Hyperlinks.Count > 0 Then cel.Hyperlinks(1).SubAddress = "Detail!H" & cel.Row - 2 End If Next End With End Sub- Thank you! I run this "report" every month and this will save me a lot of time.
The conditional formatting highlight in the dummy file isn't working correctly, and it seems the 1st time you go to the hyperlink, it goes to the wrong place. Plainly, the value displayed in cell E4 of the summary, resides in cell G2 of the detail. The hyperlink for E4 should take you to cell H2 of the detail page. I know that's what you said, but I wanted to make sure what I thought matched your explanation. Thank you again!
Did I ask this in the wrong place?
Here is a mock up of what I have. Cells E4 and E5 the hyperlink has been manually corrected. The rest of the rows are default. Thanx
