+ Reply to Thread
Results 1 to 2 of 2

Macro to write hyperlink from cell value

  1. #1
    Registered User
    Join Date
    04-22-2010
    Location
    Johannesburg SA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Macro to write hyperlink from cell value

    I run my accounts in Excel and the list of invoices is generated so that each invoice number (in the list) has a hyperlink to that invoice itself (Excel sheet) - click the hyperlink and the invoice opens - fairly straightforward.

    At the moment, I set up each hyperlink manually ...

    e.g. of invoice list (3 invoices)
    10_031_HENSWAN.xls
    10_032_AEDC.xls
    10_033_DRYDENS.xls
    To set up hyperlinks ....
    F2 - block text and copy invoice number to clipboard
    ESC
    Ctrl K (insert hyperlink)
    Paste to "address" box
    Enter
    repeat for all invoices ...... !!!!
    I have tried to write a macro to do this but the macro does not pick up the cell contents as a variable, it picks up the cell contents of the cell that is used to set up the macro.

    Macro :
    Sub Macro2()
    '' Macro2 Macro
    ''
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="10_032_AEDC.xls", _
    TextToDisplay:="10_032_AEDC.xls"
    Range("B1641").Select - (This line to move down to the next row to insert hyperlink on next cell)
    End Sub
    Is there a way I can write the macro that will copy to the clipboard the cell contents (as I go down the list) and not the cell contents of the cell that I used to write the macro. In the macro above the underlined text "10_032_AEDC.xls" needs to be the contents of the current cell, i.e. a variable.

    I have tried to use "Use Relative References" in the Macro settings but this doesn't help.

    Many thanks

    Mike 100

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro to write hyperlink from cell value

    Try something like this which adds hyperlinks to a selection.

    Please Login or Register  to view this content.
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1