Excel: entering complex formula using VBA

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

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
With Sheet17
.Range("B1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
.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"")"
End With
End Sub

This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of September 25, 2014.

For more resources for Microsoft Excel