I am relatively new to VBA, so far so good however I am stuck on a particular function which is really causing me some grief.
The macro I created uses a VLOOKUP, which references a worksheet from another workbook. The issue is, the workbook being used creates a new worksheet daily.
My Current formula (which works) is the following;
ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC,'[Unavista UTI Lookup December2019.xlsm]Dec 3'!C4:C7,4,0)"
Not to sure what the RC is about, as my starting cell is "EH2".
However, where Dec 3 is stated, that is the worksheet that changes daily in the format of "MMM D" (another macro creates this but has no impact on my macro).
So my question here is
1 - How can I reference a newly created worksheet without having to manually change the date in the VBA window
2 - Is it possible to reference a new worksheet or does it have to have the name of the worksheet?
3 - Ideally i am looking for the easiest way to write the formula
Thanks, I hope someone can provide the answer as its been bugging me for a week now!