Changing Hyperlinks in Macros

[Guest]

For a project at work, I need to add a new sheet that contains information
for a new distributor. The problem is, in the macro I have, when I try to
create a hyperlink, it will hyperlink to the same sheet every time. If I
needed to have the macro hyperlink to a new sheet, how can I do this?

I'd REALLY appreciate any help you might have...

 

[Guest]

show the revelent part of the macro and we can probably tell you how to
change it. There are multiple ways to do what you want but any given method
may not work with your set up.
we will need how you name and set up the path to the hyperlink target.

 

[Guest]

This is what I have...when I run it it says that the sheet cannot be found...


Sub Hyperlinking()
' Hyperlinking Macro

Sheets("Individual Distrib.").Select
Sheets("Individual Distrib.").Copy After:=Sheets(11)
Range("A1").Select
Sheets("Individual Distrib. (2)").Name = InputBox("Enter the number of
the new distributor.")

Sheets("Individual Distrib.").Select
Range("A11").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=InputBox("Enter the number of the new distributor.")

End Sub

 

[Guest]

The following is a typical way to generate and follow a hyperlink in VBA to a
location in the workbook:

Sub go_hyper()
Dim s As String
s = "file:///C:\Documents and Settings\ravenswood\Desktop\Book2.xls#Sheet2!B9"
ActiveWorkbook.FollowHyperlink (s)
End Sub

as you point out the sheet is always Sheet2. To hyperlink to the latest or
newest sheet, let's assume that the latest sheet is the last sheet. We get
the name of that sheet and hyperlink to that one:

Sub go_hyper_latest()
Dim s As String
n = Sheets.Count
s2 = Sheets(n).Name & "!B9"
MsgBox (s2)
s = "file:///C:\Documents and Settings\ravenswood\Desktop\Book2.xls#" & s2
ActiveWorkbook.FollowHyperlink (s)
End Sub

 

[Guest]

I think what your macro does the hyperlink address is ""

try
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=InputBox("Enter the
number of the new distributor.")
or set a variable = the new distributor name earlier in the macro and use
that variable

 

[Guest]

the subaddress, if I understand it, goes to a spot in a sheet, not to the
sheet itself