After doing a lot of cutting and pasting and sorting, the email address
blocks have a different hyperlink stored than what is displayed. When I
click on the email address, outlook opens an email that is addressed to
someone who used to be in that block.

How do I reset all the hyperlinks on the spreadsheet to what is actually in
the box?

This will probably help. Select the cells to rebuild the email links for and
then use this macro to 'rebuild' the correct links.

Sub CreateEmailLinks()
Dim anyCell As Object
'select cells to set up
'before calling this Macro
For Each anyCell In Selection
If Not IsEmpty(anyCell) Then
If LCase(Left(anyCell.Text, 7)) = "mailto:" Then
ActiveSheet.Hyperlinks.Add _
anchor:=anyCell, _
Address:=anyCell.Text
Else
ActiveSheet.Hyperlinks.Add _
anchor:=anyCell, _
Address:="mailto:" & anyCell.Text
End If
End If
Next
End Sub



"Philophil" wrote:

After doing a lot of cutting and pasting and sorting, the email address
blocks have a different hyperlink stored than what is displayed. When I
click on the email address, outlook opens an email that is addressed to
someone who used to be in that block.

How do I reset all the hyperlinks on the spreadsheet to what is actually in
the box?

That worked great. However it did not clear the hyperlink from cells that
now appear empty and still retained hyperlink data. It is easy to clear all
those, though.
Some users might have trouble setting up the macro. I finally got it
through trial and error. It had been a long time since I set up my own
macro.
phil

"Philophil" wrote:

After doing a lot of cutting and pasting and sorting, the email address
blocks have a different hyperlink stored than what is displayed. When I
click on the email address, outlook opens an email that is addressed to
someone who used to be in that block.

How do I reset all the hyperlinks on the spreadsheet to what is actually in
the box?

Sorry, didn't think to remove hyperlinks from empty-of-text cells.

Here's some code that you could run ahead of the 'rebuild hyperlinks"
section to delete all hyperlinks on the sheet:
Cells.Select
Do While Selection.Hyperlinks.Count 0
Selection.Hyperlinks.Delete
Loop
Range("A1").Select

I know it's late now, but here's a page with both short and very detailed
instructions on adding code to a workbook. This page is for generic code
placed into a regular code module, but there are links on that page to pages
that discuss Workbook and Worksheet scoped macro code. Hope it helps in the
future.



"Philophil" wrote:

That worked great. However it did not clear the hyperlink from cells that
now appear empty and still retained hyperlink data. It is easy to clear all
those, though.
Some users might have trouble setting up the macro. I finally got it
through trial and error. It had been a long time since I set up my own
macro.
phil

"Philophil" wrote:

After doing a lot of cutting and pasting and sorting, the email address
blocks have a different hyperlink stored than what is displayed. When I
click on the email address, outlook opens an email that is addressed to
someone who used to be in that block.

How do I reset all the hyperlinks on the spreadsheet to what is actually in
the box?

That remove all hyperlinks code should either be run as a separate Macro or
if you include it with the code provided before, remove the
Range("A1").Select
statement from it so that all the cells remain selected.

"Philophil" wrote:

That worked great. However it did not clear the hyperlink from cells that
now appear empty and still retained hyperlink data. It is easy to clear all
those, though.
Some users might have trouble setting up the macro. I finally got it
through trial and error. It had been a long time since I set up my own
macro.
phil

"Philophil" wrote:

After doing a lot of cutting and pasting and sorting, the email address
blocks have a different hyperlink stored than what is displayed. When I
click on the email address, outlook opens an email that is addressed to
someone who used to be in that block.

How do I reset all the hyperlinks on the spreadsheet to what is actually in
the box?