PDA

View Full Version : Add hyperlink to unknown sheet



Djblois
05-30-2007, 11:36 AM
I am creating a small macro to add a new sheet for contract information. The only problem I am having is to add a hyperlink the first sheet to the sheet that was just added.

Sub AddNewContract()
Dim NewContract As Worksheet
Dim Finalrow As Long

AddContract.Show 'Show Form

Set NewContract = Sheets.Add(, ActiveSheet) 'Add Sheet for New Contract
NewContract.Name = AddContract.tbCustNumber 'Name Sheet

'Copy Column Headings into new sheet
Sheets("1115").Range("A1:J2").Copy NewContract.Range("A1")

NewContract.Activate 'Switch to New Contract Sheet
'Add Customer Name and Salesperson to new sheet
Range("A1:B1").FormulaR1C1 = AddContract.tbCustomerName.Value
Range("E1:F1").FormulaR1C1 = AddContract.tbSalesPerson.Value

'Adjust Rows 1+2 and Column B
Rows("1:1").RowHeight = 30
Rows("2:2").RowHeight = 40
Columns("B:B").ColumnWidth = 40

'Add Sheet name and number to Main Sheet
Sheets(1).Activate
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row ' figure out last row
Cells(Finalrow + 1, 1).Value = AddContract.tbCustNumber.Value 'Add Cust#
'Add customer Name and hyperlink to contract sheet, I have tried multiple ways of doing it. I tried to put addcontract in the address and also the subaddress
ActiveSheet.Hyperlinks.Add Anchor:=Cells(Finalrow + 1, 2), Address:="" _
, SubAddress:="", TextToDisplay:=AddContract.tbCustomerName.Value

End Sub

Charlize
05-30-2007, 12:12 PM
Sub Hyperlink_to_new_sheet()
Dim v_name As String
Dim ws As Worksheet
Dim current As Worksheet
v_name = InputBox("Give name ...", "Give new contractname")
Set current = Worksheets(1)
Set ws = Worksheets.Add
ws.name = v_name
ws.Move after:=Worksheets(Worksheets.Count)
current.Activate
ActiveSheet.Hyperlinks.Add Anchor:=current.Range("A" & _
current.Range("A" & Rows.Count).End(xlUp).Row + 1), Address:="", _
SubAddress:=ws.name & "!A1", TextToDisplay:=ws.name
End Sub