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
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