Copy Link to different cell
 

Good day,

I have a column of data that are hyperlinks. The data in the column show
only the text or title of the hyperlink, but does not show the URL and only
works if clicking on the cell.

I am looking for a formula or VBA code that will start at Cell B2 and for
all cells until end of row will copy the hyperlink from the cell and paste it
in the cell next to it.

For example, if the URL for the text in B2 points to yahoo.com
and URL for text in B3 points to google.com, after running the macro, data
should appear as:

B C
yahoo http://www.yahoo.com
google http://www.google.com

Also, is there a way to make a URL that is pasted automatically appear as a
hyperlink? For example, if I copy a hyperlink from a website and paste the
URL into a cell in Excel, or if I export a table w/ URLs from Acess to Excel,
all the URLs come thru as text, and they are not active hyperlinks until I
double click on each cell and press enter. Then the content of the cell
becomes a hyperlink.


Suggestions?

Copy Link to different cell
 

Try
Sub HyperLinksShow()
Dim i
Dim LastRow
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
Cells(i, 3).Value = Cells(i, 2).Hyperlinks(1).Name
Next i
End Sub



"Brent E" wrote:

Good day,

I have a column of data that are hyperlinks. The data in the column show
only the text or title of the hyperlink, but does not show the URL and only
works if clicking on the cell.

I am looking for a formula or VBA code that will start at Cell B2 and for
all cells until end of row will copy the hyperlink from the cell and paste it
in the cell next to it.

For example, if the URL for the text in B2 points to yahoo.com
and URL for text in B3 points to google.com, after running the macro, data
should appear as:

B C
yahoo http://www.yahoo.com
google http://www.google.com

Also, is there a way to make a URL that is pasted automatically appear as a
hyperlink? For example, if I copy a hyperlink from a website and paste the
URL into a cell in Excel, or if I export a table w/ URLs from Acess to Excel,
all the URLs come thru as text, and they are not active hyperlinks until I
double click on each cell and press enter. Then the content of the cell
becomes a hyperlink.


Suggestions?

Copy Link to different cell
 

Forgot the second part... it is the way MS has designed Excel. Following
macro is suggested by MS
(http://kbalertz.com/271856/Hyperlink...mported.aspx);

'Select the range containing the hyperlinks before executing the macro
Sub HyperAdd()
'
' Converts each text hyperlink selected into a working hyperlink
'
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
'
'
End Sub


"Brent E" wrote:

Good day,

I have a column of data that are hyperlinks. The data in the column show
only the text or title of the hyperlink, but does not show the URL and only
works if clicking on the cell.

I am looking for a formula or VBA code that will start at Cell B2 and for
all cells until end of row will copy the hyperlink from the cell and paste it
in the cell next to it.

For example, if the URL for the text in B2 points to yahoo.com
and URL for text in B3 points to google.com, after running the macro, data
should appear as:

B C
yahoo http://www.yahoo.com
google http://www.google.com

Also, is there a way to make a URL that is pasted automatically appear as a
hyperlink? For example, if I copy a hyperlink from a website and paste the
URL into a cell in Excel, or if I export a table w/ URLs from Acess to Excel,
all the URLs come thru as text, and they are not active hyperlinks until I
double click on each cell and press enter. Then the content of the cell
becomes a hyperlink.


Suggestions?