VBA to create a hyperlink to sheet named in cell

[cameronem20]

Hello

I have been tearing my hair out trying to work out how to achieve this so any help would be appreciated!

I need a hyperlink created on the "CSR_Summary" sheet to open the sheet named with what is in the "Control" sheet cell A1. The hyperlink name also needs to be the value in the Control!A1 cell.

Also, how can it be so these hyperlinks will be arranged automatically one line after another (preferably in two columns and one blank line after each hyperlink, but know this would probably be hard!)

Thanks

 

[Loomah]

Hi
This should do the trick, or at least part of it (I haven't bothered with the 2 column bit as I'm not sure what you're after there)

Sub a()
Dim lRow As Long
Dim sText As String
sText = Worksheets("Control").Range("A1").Text
With Worksheets("CSR_Summary")
    lRow = .Cells(65536, 1).End(xlUp).Row
    If Not IsEmpty(Cells(lRow, 1)) Then lRow = lRow + 2
    .Hyperlinks.Add Anchor:=.Cells(lRow, 1), Address:="", SubAddress:= _
         sText & "!A1", TextToDisplay:=sText
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

Something like this ?
With Sheets("CSR_Summary")
.Hyperlinks.Add _
Anchor:=.Cells(row# , col#), _
Address:="", _
SubAddress:=Sheets("Control").Range("A1").Text & "!A1", _
TextToDisplay:=Sheets("Control").Range("A1").Text & "!A1"
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884