Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste link/hyperlink (Excel 2000)

    I want to paste a cell reference, such as ='Sheet1'!a15 to a cell on Sheet 2, and make the cell also hyperlink to the Sheet1 A15 address. Makes moving around much faster. When I have copied my A15 reference, and am on sheet2 where I want to be, if I click "Paste hyperlink" I get the cell address as a hyperlinked text. So I wind up doing PasteSpecial/PasteLink, and then Edit/Paste hyperlink on top of the link formula. All I seem to get from the macro recorder is Application.PasteLink=True. So I looked in the ObjectBrowser and thought some ActiveCell method under xlPasteType or xlPasteSpecialOperations would give me what I wanted and could use it with ActiveCell. But when I amended the recorder's text to say ActiveCell.pastelink=true, I got the "Object doesn't support the method" type error.
    So my object here is to have a nice macro that will allow me to simultaneously paste the cell reference as a link and then make it a hyperlink in one operation. What should I do? TYIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Paste link/hyperlink (Excel 2000)

    You can do it with a variation of the code I posted for you to <post#=335971>post 335971</post#> in your other question regarding Paste Link. Instead of putting in the formula you use the add hyperlink.

    Select the cell (or range) to copy, start the macro, it will prompt for the destination (it can even be in another workbook) and once you chose the desination it will create the hyperlink

    <pre>Sub PasteAsHyperlink()
    Dim sPath As String
    Dim lRow As Long
    Dim iCol As Integer
    Dim rngSelect As Range
    Dim rngDest As Range
    Set rngSelect = Selection
    Set rngDest = Application.InputBox( _
    "Where do you want to copy?", Type:=8)

    With rngSelect
    sPath = "'[" & .Parent.Parent.Name & "]" & .Parent.Name & "'!"
    For lRow = 1 To .Rows.Count
    For iCol = 1 To .Columns.Count
    rngDest.Offset(lRow - 1, iCol - 1).Hyperlinks.Add _
    Anchor:=rngDest.Offset(lRow - 1, iCol - 1), _
    Address:="", _
    SubAddress:=sPath & .Cells(lRow, iCol).Address
    Next
    Next
    End With
    End Sub</pre>


    If you want to "goto" the destination after the macro is complete add these lines before the End Sub

    <pre> With rngDest
    .Parent.Parent.Activate
    .Parent.Activate
    .Activate
    End With</pre>


    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste link/hyperlink (Excel 2000)

    If you want to display the value of the linked cell(s) in the target instead of the link address, here is a slight expansion of Steve's excellent code:

    Sub PasteAsHyperlink()
    Dim sPath As String
    Dim sSubAddr As String
    Dim lRow As Long
    Dim iCol As Integer
    Dim rngSelect As Range
    Dim rngDest As Range

    Set rngSelect = Selection
    Set rngDest = Application.InputBox( _
    "Where do you want to copy?", Type:=8)

    With rngSelect
    sPath = "'[" & .Parent.Parent.Name & "]" & .Parent.Name & "'!"
    For lRow = 1 To .Rows.Count
    For iCol = 1 To .Columns.Count
    sSubAddr = sPath & .Cells(lRow, iCol).Address
    rngDest.Offset(lRow - 1, iCol - 1).Hyperlinks.Add _
    Anchor:=rngDest.Offset(lRow - 1, iCol - 1), _
    Address:="", _
    SubAddress:=sSubAddr, _
    TextToDisplay:="=" & sSubAddr
    Next
    Next
    End With
    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Paste link/hyperlink (Excel 2000)

    TextToDisplay must be a new parameter in XL2000. It is not in the version I have with XL97.

    In XL97, the "displayed text" is what you have listed in the sSubAddr variable as the default. Does XL2000 put a different value as the displayed text?

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste link/hyperlink (Excel 2000)

    I don't know whether TextToDisplay was introduced in 2000 or 2002, but it is not explicitly mentioned under "What's New" in the online help for 2002. If you omit this parameter, the displayed text is the same as the SubAddress.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Paste link/hyperlink (Excel 2000)

    Well, Steve was quicker than I was, but my code looks just a little different so I'll post it. I took longer because I was trying to get the address of a pasted cell from the clipboard, but it appears that only Excel can do that. You could fake it by saving the address with the deactivation event, but that would be way too much overhead, so I just prompt for both the source and destination. HTH --Sam<pre>Option Explicit

    Sub pasteHyper()
    Dim rngIn As Range, rngOut As Range
    Dim strIn As String
    strIn = ActiveSheet.Name & "!" & ActiveCell.Address
    Set rngIn = Application.InputBox( _
    prompt:="Select the anchor cell to link to.", _
    Default:=strIn, Title:="Source", Type:=8)
    Set rngOut = Application.InputBox( _
    prompt:="Select the destination cell for the hyperlink.", _
    Title:="Destination", Type:=8)
    rngOut.Parent.Activate
    rngOut.Select
    strIn = rngIn.Parent.Name & "!" & rngIn.Address
    ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:="", _
    SubAddress:=strIn, TextToDisplay:="=" & strIn
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Paste link/hyperlink (Excel 2000)

    I could find no way to look at the clipboard contents without pasting first.

    You might want to include the single quote around the sheet name in case it has spaces (it often does)
    strIn = "'" & rngIn.Parent.Name & "'!" & rngIn.Address

    You will also have a "problem" if they select a cell from a different workbook, since it does not include the workbook name. the input box allows selecting other workbooks.

    Steve

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Paste link/hyperlink (Excel 2000)

    > I could find no way to look at the clipboard contents without pasting first.
    But, even if you paste, you cannot get the source address, can you?

    >You might want to include the single quote around the sheet name
    Yes, definitely

    > You will also have a "problem" if they select a cell from a different workbook
    Not a problem, a "feature!" <img src=/S/grin.gif border=0 alt=grin width=15 height=15> There is also a problem if they select more than a single cell.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Paste link/hyperlink (Excel 2000)

    Yes my code will create a "range of hyperlinks" if you select a range initially. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    It only uses the first cell of the destination if you select more than 1 destination cell.

    Both have the "bonus feature" of giving a "run-time error" if the user tries to cancel the selection. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link/hyperlink (Excel 2000)

    Thanks for the great responses! I want to study each set of codes and understand what it does. I thought I was on the right track but it looks as if there was more to it than I imagined. I create lots of lead schedule tabs with supporting schedule tabs behind it. They might have tabs behind them. Somewhere on the supporting tab I come up with a total and I create a cell formula to reference it on the lead schedule. But I also want to have a hyperlink there, so that I and other users can quickly move to the schedule in the workbook, wherever it is buried. Usually these are one cell items, but not always. I'll experiment and see how it goes. This could be extremely useful in speeding up the process of moving around workbooks, so perhaps it will have general usefulness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •