Creating One Hyperlink At A Time In Vba

jeddell · Jan 14th 2007

Hi all,

I have spent the better half of today searching your forum for a solution to this but end up with finding code that creates hyperlinks by the column or row.

I Have a spreadsheet with some code which archives data into a summary sheet and creates a backup sheet of the detail. This all works really well.

In the summary sheet, I have a column that is named with the name of the new worksheet.

What I want to do is to be able to create a hyperlink from this name in the column to the new, backed up worksheet.

I do not want to create a list of hyper links, I only want to create on link at a time, each time I run the code.

Thanks in advance
Cheers

Dean

MrkFrrl · Jan 14th 2007

Re: Creating One Hyperlink At A Time In Vba

I'm sowewhat confused about what you're asking. Are you generating new names each time in the Summary sheet to be used as a reference for the creation of a new sheet?

Here's something I just put together to add a sheet and then put in a hyperlink to it from a sheet named Summary. It seems to work OK, but you have to remove Option Explicit from the top of the module's header. I don't know why; I must be overlooking something and don't have the time to figure out why. In any case, maybe it's better in a module by itself for that reason.

Code

Sub AddHyperLinkedSheet()
' use the process to determine whatever row you want to use to assign
' the sheet's name based on the particular row of the Column A in the Summary sheet.
' Here I assign x to whatever value would be in cell A4 of the Summary sheet.
x = 4 


    Sheets.Add
    ActiveSheet.Name = Sheets("Summary").Range("A" & x).Value
    MyShtNm = ActiveSheet.Name
    Sheets("Summary").Select
    Range("A" & x).Select
    Sheets("Summary").Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        MyShtNm & "!A1"
        
End Sub

Display More

Quote from jeddell

Hi all,

I have spent the better half of today searching your forum for a solution to this but end up with finding code that creates hyperlinks by the column or row.

I Have a spreadsheet with some code which archives data into a summary sheet and creates a backup sheet of the detail. This all works really well.

In the summary sheet, I have a column that is named with the name of the new worksheet.

What I want to do is to be able to create a hyperlink from this name in the column to the new, backed up worksheet.

I do not want to create a list of hyper links, I only want to create on link at a time, each time I run the code.

Thanks in advance
Cheers

Dean

Display More

jeddell · Jan 14th 2007

Re: Creating One Hyperlink At A Time In Vba

MrkFrrl, Thankyou for your reply.

I read your code and it's not quite what I'm After. I'll try and step you through it.

1. I have two sheets as standard sheets "Summaries" and "Current Trades".

2.

jeddell · Jan 14th 2007

Re: Creating One Hyperlink At A Time In Vba

MrkFrrl, Thankyou for your reply.

I read your code and it's not quite what I'm After. I'll try and step you through it.

1. I have two sheets as standard sheets "Summaries" and "Current Trades".

2. When I fill up the sheet "Current Trades", I click on a button to archive the sheet. This pulls the summary data from the "Current Trades" Sheet and places it into the first available row of the "Summaries" Sheet. It then loops down one of the columns in the summary sheet to find the first blank cell in that column. This will coincide with the data just transfered over. Once there, the code will place a number in the cell and record the number as a string.
3. The code then creates a new sheet and names it after the number it just placed in the empty cell in the "Summaries" Sheet. Now I have three sheets, "Summaries", "Current Trades" and "0" (Being the first archive task.)

What I want to do is place a hyperlink from the cell with "0" in the column located in the "Summaries" sheet to the NEW sheet named"0". This basically makes a link from the summary in that paricular row, to the sheet with all of the detail in it.

So. All I need is some VBA Code that will let me create a Hyperlink to a VBA generated name of a sheet.

Hope this clarifies it.

In your code you specifically named a sheet. My code names the sheet for me so I need to figure out how to get that name into the hyperlink code.

Hope this clarifies it for you

Cheers

snoopypotato · Apr 23rd 2013

Re: Creating One Hyperlink At A Time In Vba

HI Mrkfrrl

I try your code to create the hyperlink and when I click the link cell and it show the warning message: Reference is not valid.
Is there somewhere wrong that I miss?

Creating One Hyperlink At A Time In Vba

Participate now!

Share