Hi,

Firstly, thanks to everyone who has posted on here. I have frequently trawled the forums as a guest trying to solve problems and this has been a mine of information.

Scenario:
I am working on a macro that utilises two mirror workbooks, a current one and one from the week previous. The user opens the previous Workbook via the macro. At this point I would like the macro to insert an Index Match formula into a specific cell and perform the calculation. The name of the previous Workbook will change and as such I use the Macro to define the name of this Workbook within the formula. The formula itself is made up of dynamic named ranges that will automatically calculate for their own workbook. With the code I have I keep receiving the "Compile Error: Expected end of Statement / Syntax error" on the line containing the formula itself. Hopefully, I have explained my situation clearly enough, if not please let me know.

The code I have so far is as follows:

Sub GetName()
'Defining Things
    Dim wbCurr As Workbook
    Dim wbPrev As Workbook
    Dim PrevFile As String
    Dim Bookname As String
    Dim Del As Long
    
'Define Current Workbook
    Set wbCurr = ThisWorkbook
    
'Define and Open Previous Workbook
    PrevFile = Application.GetOpenFilename
    On Error GoTo ErrMsg1
    If PrevFile <> "" Then
        Workbooks.Open PrevFile
        Set wbPrev = ActiveWorkbook
    End If

'Enter Formula
Bookname = wbPrev.Name
wbCurr.Range("o21").Formula= "=INDEX('["&Bookname&"]'!WB_Range_WSPlannedDeliveryDate,MATCH(WB_Val_WSContainerNumber,'["&Bookname&"]'!WB_Range_WSContainerNo,0))"

'Select Current Workbook and Close Previous Workbook
    'wbCurr.Activate
    'wbPrev.Close
    
'Delete "Delivered" Status Lines
    'For Del = Range("T" & Rows.Count).End(xlUp).Row To 2 Step -1
        'If Range("T" & Del).Value = "Delivered" Then
            'Rows(Del).EntireRow.Delete
        'End If
    'Next Del
    
Exit Sub

ErrMsg1:
MsgBox ("There is no available previous file for comparison."), , "Model Error"

End Sub
In case anyone is wondering, I know I have sections commented out that I have already created but not utilising whilst testing.

Thanks for any and all help.

Jamie