Create Hyperlink in a Cell to a Worksheet Name

[pablo_nj]

Hello -

I have an excel worksheet of about 150 lines(inventory control project). Column B in this sheet contains unique serial numbers. I have written a macro to create 150 separate worksheets and the name of each worksheet automatically comes from each unique serial number in column B. All this is done in the macro.

Problem: I would like to create hyperlinks(using my macro) in Column B that point to the worksheet with the same name. So for instance, if the serial number in cell B1 is 12F3, then I would like to create a hyperlink in cell B1 that points to the worksheet named "12F3". Any ideas?

 

[Kenneth Hobson]

Welcome to the forum!

Sub Main()
  Dim s As String
  s = "12F3"
  
  With Sheet1
    .Hyperlinks.Add Anchor:=.Range("B2"), Address:="", _
      SubAddress:="'" & s & "'!A1", TextToDisplay:=s
  End With
End Sub

 

[pablo_nj]

Thanks Kenneth - this is close but instead of defining a constant s, I'd like to do it a little differently. Let me describe what I'm doing. I have a loop that counts the # of lines in my inventory(INV_CNT) and then does the following(below is not code....just narative:

For i=1 to INV_CNT
- Select Inventory Worksheet
- Get Serial Number from cell(i,2).value
- Create new worksheet with the name = Serial Number
Next i

What I would LIKE to do is to create the hyperlink in cell(i,2) to the new worksheet I just created. So, can't you a constant. Need to be able to read and then assign the worksheet name in the hyperlink command. Any help would be appreciated!

Thanks!

Paul

Welcome to the forum!

Sub Main()
  Dim s As String
  s = "12F3"
  
  With Sheet1
    .Hyperlinks.Add Anchor:=.Range("B2"), Address:="", _
      SubAddress:="'" & s & "'!A1", TextToDisplay:=s
  End With
End Sub

 

[VanillaSky]

There is another way to solve this problem without using macro.

=HYPERLINK(CONCATENATE("#";B2;"!A1")"name_for_your_link")

After that simply drag and copy the formula for all desired cells.

advantages: fast and simple
disadvantages: you will need to create separate column for those hyperlinks

 

[Kenneth Hobson]

S is not a constant. It is the worksheet's name. You have that in your loop when you create the worksheet.

 

[pablo_nj]

Got it - let me give this a try!

Thanks - Paul

S is not a constant. It is the worksheet's name. You have that in your loop when you create the worksheet.