pulling a list of hyperlinks from a worksheet

[Dale]

I have a worksheet with over 800 cells. Each cell displays a churches
website url. I need to display the actual hyperlink http://www.whatever.com
not the Link name.
example First Baptist Church Nashville. I need the hyperlink
http://fbcnashville.org. displayed in the next cell.

 

[Luke M]

'Here's a short macro:
'Note that this assumes you are not using the function HYPERLINK, but just
an 'embedded hyperlink
'To install, right click on sheet tab, view code, paste the following in.

Sub ListHyperlinks()
i = 1
For Each h In Me.Hyperlinks
'Generates list in column Z, starting at row 1
Cells(i, "Z").Value = h.Name
i = i + 1
Next h
End Sub

 

[Don Guillett]

If you have this http://fbcnashville.org/

typed into a cell you can just use
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
ActiveWorkbook.FollowHyperlink Address:=Target
End Sub

 

[JLatham]

See if this doesn't do the job for you:

Sub ExtractHyperlinks()
'change these Const values as needed
Const theWorksheetName = "Salary.com"
'the column with hyperlink text
Const hLinkColumn = "B"
'column to put link only into
Const hLinkOnlyCol = "AI"

Dim myWS As Worksheet
Dim linksRange As Range
Dim anyLink As Range

Set myWS = Worksheets(theWorksheetName)
Set linksRange = myWS.Range(hLinkColumn & "1:" & _
myWS.Range(hLinkColumn & Rows.Count).End(xlUp).Address)
For Each anyLink In linksRange
If anyLink.Hyperlinks.Count > 0 Then
myWS.Range(hLinkOnlyCol & anyLink.Row) = _
anyLink.Hyperlinks(1).Address
End If
Next
Set linksRange = Nothing
Set myWS = Nothing
MsgBox "Task Completed"
End Sub