pulling a list of hyperlinks from a worksheet

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.

'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

--
Best Regards,

Luke M
"Dale" wrote in message
...
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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dale" wrote in message
...
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.

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


"Dale" wrote:

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.