Copy/Paste Hyperlink Address
 

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.

Copy/Paste Hyperlink Address
 

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)

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

Copy/Paste Hyperlink Address
 

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
--
Gary''s Student
gsnu200701


"Bonnie" wrote:

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.

Copy/Paste Hyperlink Address
 

try this

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

--
Don Guillett
SalesAid Software

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

Copy/Paste Hyperlink Address
 

Im getting an error on this - Object doesn't support this property our method.

Thanks

"Chip Pearson" wrote:

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)

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

Copy/Paste Hyperlink Address
 

Hi DOn,

Looks like pasting the value only gives me the friendly name of the hyperlink.

Thanks,
Bonnie

"Don Guillett" wrote:

try this

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

--
Don Guillett
SalesAid Software

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

Copy/Paste Hyperlink Address
 

Thanks very much Gary. That did the trick.

Bonnie

"Gary''s Student" wrote:

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
--
Gary''s Student
gsnu200701


"Bonnie" wrote:

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.

Copy/Paste Hyperlink Address
 

You are very welcome !
--
Gary's Student
gsnu200701


"Bonnie" wrote:

Thanks very much Gary. That did the trick.

Bonnie

"Gary''s Student" wrote:

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
--
Gary''s Student
gsnu200701


"Bonnie" wrote:

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.