VBA Vlookup a filename

Question

I'm trying to include a Dim in a Vlookup.

Dim filename As String
filename = Format(DateAdd("d", -6, Now()), "mm-dd-yy")
filename = "NSC " & filename & ".xlsm"

For Each ws In ActiveWorkbook.Worksheets
        ws.Activate

        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[-2],'[" & filename & "]" & ws & "'!R8C4:R500C6,3,FALSE)"
Next

My error comes at filename in the vlookup. I knew it would cause issues but I'm not sure how to fix it. Filename will look something like "NSC 06-03-15.xlsm" and I cannot just type that in as the date changes each week.

Any solutions?

Thank you.

EDIT: This worksheet is already open.

Answer 1

You need to be using the name of the sheet, not the object itself.

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[" & filename & "]" & ws.Name & "'!R8C4:R500C6,3,FALSE)"