How do I create dynamic hyperlinks to new Excel worksheets? 1

[searlerm]

I have drafted a macro to create new sheets in a workbook which are named as per the user entry into a text box on a user form. This works fine but I also want to create a hyperlink "Contents" type table pointing to each sheet as part of the macro. I can do this using a line as follows where "Depositos" is the name of each new sheet:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=Depositor & "!A1", TextToDisplay:=UCase(Depositor)

However, this doesn't work where I have spaces in the sheet name. Any idea how I can embed inverted commas into a formula or am I barking up the wrong tree?

Any help greatfully welcomed!
Thanks

 

[SkipVought]

Hi,

You need tics around the sheet name...

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
        SubAddress:="'" & Depositor & "'!A1", TextToDisplay:=UCase(Depositor)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com