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
Bookmarks