Hi All
I've been on this for hours and there must be an easier way to do this but I'd like to persevere with the formula route if possible
I'm attempting to enter a formula via vba to add a hyper link, I've been going around in circles for what seems like a lifetime (probably about 2 hours) and cant seem to get it right.
A couple of question
is it possible to do this??
if it is, any ideas where I'm going wrong??
Any help would be appreciated
Kind regards
Paul
I've been on this for hours and there must be an easier way to do this but I'd like to persevere with the formula route if possible
I'm attempting to enter a formula via vba to add a hyper link, I've been going around in circles for what seems like a lifetime (probably about 2 hours) and cant seem to get it right.
A couple of question
is it possible to do this??
if it is, any ideas where I'm going wrong??
Any help would be appreciated
Kind regards
Paul
Code:
Sub create_new_sheet()
Sheet1.Unprotect Password:="password1"
Sheet1.Range("K3") = Sheet2.Range("X7").Value
Dim strName As String
Dim wk As Worksheet
strName = Sheet2.Range("X7")
On Error Resume Next
Set wk = Worksheets(strName)
On Error GoTo 0
If wk Is Nothing Then
Sheet1.Visible = True
Sheet1.Copy After:=Worksheets(Worksheets.Count)
NewPageName = Sheet2.Range("X7")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheet16.Visible = False
Else: MsgBox "Project Already Exists", vbExclamation, "Project Exists"
Exit Sub
End If
'add hyperlink
Sheet2.Range("X7").Copy
With Sheet17
.Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
[COLOR=#ff0000].Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(0, 1).Formula = "=HYPERLINK(CELL(""LEFT(CELL(""filename""),FIND(""]"",CELL(""filename""),1))"")&""-'" & "C" & ActiveCell.Row & "'!A1"",""My Name"")"[/COLOR]
End With
'info_to_register
End Sub