Copy/Paste Hyperlink Address

[Guest]

Hi,

I am trying to make a macro that willl copy a column containing hyperlinks
and past only the address portion of the hyperlink to another column.

Here's what I have:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/14/2007 by Bonnie Hicks
'

'
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Columns("J:J").Select
ActiveSheet.Paste
End Sub


Can anyone tell me if I can change the Paste line to only include the
hyperlink address or another way to do this.

Thanks in advance.

 

[Chip Pearson]

Use something like

Range("A1:A10").Copy
Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode = False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
End If
End Function

So that in J1 enter:

=hyp(B1) and copy down

 

[Don Guillett]

try this

Sub pasteval()
columns("j").value = columns("b").value
End Sub

 

[Guest]

Thanks very much Gary. That did the trick.

Bonnie

 

[Guest]

You are very welcome !