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.
You need to be using the name of the sheet, not the object itself.