Use current sheet name in Macro
 

Matt,

Something like this should work, though I wasn't sure where you wanted
the hyperlink to point: this should give you an idea of what to do in
any event.....

Sub Add_Hyperlinks_to_Branch_sheet2()
Dim SheetName As String
Dim BranchName As String

SheetName = ActiveSheet.Name
BranchName = Range("A1").Value

ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="",
SubAddress:= _
"'" & SheetName & "'!A10", TextToDisplay:=BranchName

End Sub

Although you don't need to use variables at all:

Sub Add_Hyperlinks_to_Branch_sheet3()
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="",
SubAddress:= _
"'" & ActiveSheet.Name & "'!A10",
TextToDisplay:=Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

"Matthew Wood" wrote in message
...
Hi,

I have a worksheet that I want to creat a number of hyperlinks using
a macro. I created a macro by recording actions a sample is shown
below. The sheetname I created the macro in is called 'Branch #33-48'
and the contents of the cell that I 'goto' (R1CI) is 'Branch #33'.

What I want to do is create lots of different sheets and run the
macro against all of them. The problem is I would need to keep
editing the macro replacing the sheetname and cell contents.

Is there a way that I can declare SHEETNAME and BRANCHNAME as
variables assign the current sheetname to SHEETNAME and contents of a
cell to BRANCHNAME and then use the variables as input to
ActiveSheet.Hyperlinks.Add Anchor function.

Thanks

Matt

Sub Add_Hyperlinks_to_Branch_sheet()
'
' Add_Hyperlinks_to_Branch_sheet Macro
' Macro recorded 19/01/2004 by Matthew Wood
'
'
Application.Goto Reference:="R1C1"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
"'Branch #33-48'!A10", TextToDisplay:="Branch #33"
Range("D12").Select

End Sub