VLookup to lookup the left worksheet

Question

I am struggling to get the below formula to work to look up the worksheet to the left. Each new tab created daily will consist of the current day, looking up the values entered the previous day (this being the worksheet to the left) And a new worksheet created each day. For example, sheet name will change from ‘02feb’ to 03feb, 04feb etc, as the macro will be run each morning.

ActiveCell.Formula = "=VLookup(c2,'02feb'!C:H,6,0)"

I keep getting an active window open to locate the file and don’t know what to do.

Thank you.