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.

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.

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.

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.

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.

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.

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.

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.