Vba to insert index match formula referencing external workbook

[jumbledore]

Hi, I have created a vba code that should insert the index/match formula into a cell and the data for the match function is found in another workbook. However the name of this workbook is saved in another excel file.

I would like it to be pasted as an excel formula. I can easily achieve the desired result using the Worksheet function in vba but the problem with this is that the formula cannot be copied and pasted within the spreadsheet.


Here's the code that I have been working on:

Sub macro1()

Columns("A:A").EntireColumn.Autofit
Range("B1").Select
ChDir "C:\filename.csv"
Set wb=Selection 'the name of the data file
Set wks = wb.Sheets(1)

Windows("filename.csv" ).Activate
ActiveWindow.Close

Range("B1").Select

' here's where I am getting stuck at:

ActiveCell.FormulaR1C1 = _

"=INDEX('" & wks.name & "'! A:A, MATCH (A1, '" " & wks.name &" '! $K:K, 0),)"

Range("B2").Select

End Sub