VBA code to Hyperlink

[MrDoc]

Hi,

I'm writing a macro to create a new worksheet, using as template another worksheet in the same workbook. No problems here. Then I tried to create a hyperlink in the newly created and renamed worksheet and got nowhere. Either an error occurs, or the hyperlink is created in the new worksheet but still points to the template worksheet.

I assigned a variable to the new worksheet's name (MyName), but still I have a problem with this line of code (at least):


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=?, SubAddress:= _
?, TextToDisplay:="Month Stats"

Does anybody know a solution? I searched and searched, but found nothing yet.

Thanks for any help,

MrDoc

 

[Norie]

You don't seem to be using the variable you assigned the new worksheet to in that code.

Also, using ActiveSheet and Selection is not a good idea here - that might even be the reason the hyperlink is ending up in the wrong place.

 

[MrDoc]

Thanks for replying, Norie.

I forgot to mention that the hyperlink is to a cell in the newly created worksheet.

I just put that line of code code in my message to try to explain my difficulty, hence the ? marks. I've been replacing the ??? with variables, but didn't get it.

ActiveSheet is the worksheet where I'm trying to create the hyperlink. And the hyperlink is really created in that worksheet, except that it points to a cell in the template sheet, not to a cell in the newly created sheet. Do you have an idea of what I must do?

Regards,
MrDoc

 

[Norie]

Why are you using ActiveSheet instead of the variable, MyName?

If MyName refers in some way to the worksheet you want to put the hyperlink on it would be better to use that than ActiveSheet.

ActiveSheet is basically whatever VBA/Excel regards as the active sheet.

As for the rest of the code, where do you actually want to put the hyperlink on the new worksheet?

Where should it link to?

What text do you want to appear for the hyperlink....

 

[MrDoc]

Norie,

I want to place the hyperlink in cell A4, it must link to cell A157 in the same worksheet (newly created by the macro from a template sheet in the same workbook) and the text for the title can be "Monthly Stats".

Thanks a lot for helping.
MrDoc

 

[Norie]

Well the only thing missing is how you are creating the new worksheet and what the variable MyName refers to.

Can you post the code for that?

 

[AlphaFrog]

Maybe try something like this...

    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Range("A4"), Address:="", SubAddress:= _
        .Name & "!A157", TextToDisplay:="Monthly Stats"
    End With

 

[MrDoc]

Norie and AlphaFrog!

The code AlphaFrog posted works fine, my mistake lied in the Address and SubAddress parts of the hyperlink! I really didn't know what to put where... and all I tried didn't work. It also works if I replace .name with the variable Myname as the name of the new sheet where the hyperlink is to be created.

Thanks a lot!

Regards,
MrDoc