Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 74
Default 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?





  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default 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?





  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default 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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check box cell link - copy problem mailrail Excel Discussion (Misc queries) 3 November 8th 06 08:24 PM
Copy and link formats from cell to cell Kathrine J Wathne Excel Discussion (Misc queries) 0 June 15th 06 03:54 PM
Link-to-cell copy problem Squeaky Links and Linking in Excel 0 September 12th 05 01:59 PM
Copy Paste Special Link Cell Problem cpetta Links and Linking in Excel 6 July 2nd 05 12:58 AM
copy combobox - cell link to change automatically Bojana Excel Worksheet Functions 1 June 8th 05 02:35 PM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"